permissions via roles query (SQL Server 2000)

Is anybody willing to share a query which shows all permissions granted
to a user, including permissions granted via roles? The complexity is
that a role can be granted to a role, and therefore this becomes a
bill-of-materials explosion / tree hierarchy / adjacency list problem.
Example:
create role r1
grant select on t1 to r1
grant select on t2 to r1
create role r2
grant select on t3 to r2
grant select on t4 to r2
create role r3
grant r1 to r3
grant r2 to r3
grant r3 to user1
The end result is that you want to be able to see that user1 has select
on t1, t2, t3, t4. An added bonus would be able to see the lineage.
I have found general discussion about solving these kinds of problems.
I'm curious if anybody has a working example for permissions and roles
in SQLServer 2000.sp_helprotect
<rc8740@.netscape.net> wrote in message
news:1149690495.671940.102730@.i39g2000cwa.googlegroups.com...
> Is anybody willing to share a query which shows all permissions granted
> to a user, including permissions granted via roles? The complexity is
> that a role can be granted to a role, and therefore this becomes a
> bill-of-materials explosion / tree hierarchy / adjacency list problem.
> Example:
> create role r1
> grant select on t1 to r1
> grant select on t2 to r1
> create role r2
> grant select on t3 to r2
> grant select on t4 to r2
> create role r3
> grant r1 to r3
> grant r2 to r3
> grant r3 to user1
> The end result is that you want to be able to see that user1 has select
> on t1, t2, t3, t4. An added bonus would be able to see the lineage.
> I have found general discussion about solving these kinds of problems.
> I'm curious if anybody has a working example for permissions and roles
> in SQLServer 2000.
>|||There are some good ones up on sqlservercentral.com
It sounds like you may be looking for one like this one:
http://www.sqlservercentral.com/scr...butions/268.asp
-Sue
On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:

>Is anybody willing to share a query which shows all permissions granted
>to a user, including permissions granted via roles? The complexity is
>that a role can be granted to a role, and therefore this becomes a
>bill-of-materials explosion / tree hierarchy / adjacency list problem.
>Example:
>create role r1
>grant select on t1 to r1
>grant select on t2 to r1
>create role r2
>grant select on t3 to r2
>grant select on t4 to r2
>create role r3
>grant r1 to r3
>grant r2 to r3
>grant r3 to user1
>The end result is that you want to be able to see that user1 has select
>on t1, t2, t3, t4. An added bonus would be able to see the lineage.
>I have found general discussion about solving these kinds of problems.
>I'm curious if anybody has a working example for permissions and roles
>in SQLServer 2000.|||There are some good ones up on sqlservercentral.com
It sounds like you may be looking for one like this one:
http://www.sqlservercentral.com/scr...butions/268.asp
-Sue
On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:

>Is anybody willing to share a query which shows all permissions granted
>to a user, including permissions granted via roles? The complexity is
>that a role can be granted to a role, and therefore this becomes a
>bill-of-materials explosion / tree hierarchy / adjacency list problem.
>Example:
>create role r1
>grant select on t1 to r1
>grant select on t2 to r1
>create role r2
>grant select on t3 to r2
>grant select on t4 to r2
>create role r3
>grant r1 to r3
>grant r2 to r3
>grant r3 to user1
>The end result is that you want to be able to see that user1 has select
>on t1, t2, t3, t4. An added bonus would be able to see the lineage.
>I have found general discussion about solving these kinds of problems.
>I'm curious if anybody has a working example for permissions and roles
>in SQLServer 2000.|||I was really hopeful when I found this...but I'm getting zero records when
I run the SP created by the script.... any other tools or suggestions?
Thanks.
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:u7sr8217iivmuojp2cq7msh6mc7vplde0f@.
4ax.com...
> There are some good ones up on sqlservercentral.com
> It sounds like you may be looking for one like this one:
> http://www.sqlservercentral.com/scr...butions/268.asp
> -Sue
> On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:
>
>|||I was really hopeful when I found this...but I'm getting zero records when
I run the SP created by the script.... any other tools or suggestions?
Thanks.
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:u7sr8217iivmuojp2cq7msh6mc7vplde0f@.
4ax.com...
> There are some good ones up on sqlservercentral.com
> It sounds like you may be looking for one like this one:
> http://www.sqlservercentral.com/scr...butions/268.asp
> -Sue
> On 7 Jun 2006 07:28:15 -0700, rc8740@.netscape.net wrote:
>
>

Permissions

They removed the share off of the drive where my snapshot replication step
(SS 2000 SP3a) writes to. That's all well and good because someone had
accidently shared out the whole drive. But now my snapshot step is failing
with "process could not create \\server\h\repl\unc".
Of course, we added a share to the repl folder and it failed with the same
error msg. Then we added in the share to the whole drive again and it still
failed.
So I am wondering if some permissions got clobbered or changed. So here are
my q's:
1. What permissions does snapshot replication run under? Does it just run
under the main sql server service account?
2. Can we just share out the subfolder, i.e. either the repl or the unc
subfolder?
3. Is there anything else we should be troubleshooting?
Any help would be much appreciated...Nevermind - I got it to work.
"CLM" wrote:

> They removed the share off of the drive where my snapshot replication step
> (SS 2000 SP3a) writes to. That's all well and good because someone had
> accidently shared out the whole drive. But now my snapshot step is failin
g
> with "process could not create \\server\h\repl\unc".
> Of course, we added a share to the repl folder and it failed with the same
> error msg. Then we added in the share to the whole drive again and it sti
ll
> failed.
> So I am wondering if some permissions got clobbered or changed. So here a
re
> my q's:
> 1. What permissions does snapshot replication run under? Does it just ru
n
> under the main sql server service account?
> 2. Can we just share out the subfolder, i.e. either the repl or the unc
> subfolder?
> 3. Is there anything else we should be troubleshooting?
> Any help would be much appreciated...

Permissions

They removed the share off of the drive where my snapshot replication step
(SS 2000 SP3a) writes to. That's all well and good because someone had
accidently shared out the whole drive. But now my snapshot step is failing
with "process could not create \\server\h\repl\unc".
Of course, we added a share to the repl folder and it failed with the same
error msg. Then we added in the share to the whole drive again and it still
failed.
So I am wondering if some permissions got clobbered or changed. So here are
my q's:
1. What permissions does snapshot replication run under? Does it just run
under the main sql server service account?
2. Can we just share out the subfolder, i.e. either the repl or the unc
subfolder?
3. Is there anything else we should be troubleshooting?
Any help would be much appreciated...Nevermind - I got it to work.
"CLM" wrote:
> They removed the share off of the drive where my snapshot replication step
> (SS 2000 SP3a) writes to. That's all well and good because someone had
> accidently shared out the whole drive. But now my snapshot step is failing
> with "process could not create \\server\h\repl\unc".
> Of course, we added a share to the repl folder and it failed with the same
> error msg. Then we added in the share to the whole drive again and it still
> failed.
> So I am wondering if some permissions got clobbered or changed. So here are
> my q's:
> 1. What permissions does snapshot replication run under? Does it just run
> under the main sql server service account?
> 2. Can we just share out the subfolder, i.e. either the repl or the unc
> subfolder?
> 3. Is there anything else we should be troubleshooting?
> Any help would be much appreciated...