Re: permissions via roles query (SQL Server 2000)



sp_helprotect




<rc8740@xxxxxxxxxxxx> wrote in message
news:1149690495.671940.102730@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.



Relevant Pages

  • Re: Role/Privilege and remote databases
    ... The general rule is that "privs acquired via ROLEs do NOT apply within ... There is nothing unique or different regarding how permissions are handled ... GRANT EXECUTE ON stg.load_package TO load_role ... GRANT load_role TO user1 ...
    (comp.databases.oracle.server)
  • Issue running DBMS_MVIEW.REFRESH
    ... I have created a materialized view but I am having issues executing the ... CREATE USER &USER1 ... ... GRANT CREATE SESSION TO &USER1; ... GRANT CREATE SESSION TO &USER2; ...
    (comp.databases.oracle.server)
  • Re: With statement alias problem/10g
    ... columns, rather than grant select on the whole underlying table, I ... scott@ORA92> CREATE USER USER1 IDENTIFIED BY USER1 DEFAULT TABLESPACE ... scott@ORA92> CREATE USER USER2 IDENTIFIED BY USER2 DEFAULT TABLESPACE ... user1@ORA92> CREATE OR REPLACE VIEW testView3 AS ...
    (comp.databases.oracle.server)
  • Re: Issue running DBMS_MVIEW.REFRESH
    ... Paul wrote: ... GRANT CREATE TRIGGER TO &USER1; ... GRANT ALTER ANY TABLE TO &USER2;* ...
    (comp.databases.oracle.server)
  • permissions via roles query (SQL Server 2000)
    ... Is anybody willing to share a query which shows all permissions granted ... The complexity is ... grant select on t1 to r1 ... The end result is that you want to be able to see that user1 has select ...
    (microsoft.public.sqlserver.security)