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:
>
>