Re: permissions via roles query (SQL Server 2000)


<rc8740@xxxxxxxxxxxx> wrote in message
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.


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.