Down the Rabbit [Role]?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 29, 2012 at 5:50 am

  #263093

  I'm playing with discovering permissions, and having trouble constructing a recursive CTE that will give me, say, my own principal_id, and all the roles i directly or indirectly belong to.

  this query gives me my own id, plus all the roles i've been directly assigned to.

  SELECT

  USER_ID() AS id

  UNION ALL

  SELECT

  rolezx.role_principal_id

  FROM sys.database_principals userzx

  LEFT OUTER JOIN sys.database_role_members rolezx

  ON userzx.principal_id = rolezx.member_principal_id

  LEFT OUTER JOIN sys.database_principals decripz

  ON rolezx.role_principal_id = decripz.principal_id

  WHERE userzx.name = USER_NAME()

  so my starting point is this query, that gives me all the users and the roles they belong to, but that's where i'm stuck;

  how do i use (what i think is the right query)

  below as a recursive CTE to find all the roles i belong to, or the roles that my roles belong to?

  ;WITH AllRoles

  AS

  (

  SELECT

  userz.name,

  userz.principal_id,

  userz.type_desc,

  CONVERT(int,NULL) As role_principal_id

  FROM sys.database_principals userz

  UNION

  SELECT

  userz.name,

  rolez.member_principal_id,

  userz.type_desc,

  role_principal_id

  FROM sys.database_role_members rolez

  INNER JOIN sys.database_principals userz

  ON userz.principal_id = rolez.member_principal_id

  )

  select * from AllRoles

  for testing, i was using this structure of roles and a single user for testing, so in the end, i wanted to see that my user "ClarkKent", eventually belongs to all teh roles below.

  multi level role example:

  CREATE ROLE [GrantPa]

  GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [GrantPa]

  CREATE ROLE [GrantMa]

  EXEC sp_addrolemember N'db_datareader', N'GrantMa'

  CREATE ROLE [ParentA]

  EXEC sp_addrolemember N'GrantPa', N'ParentA'

  EXEC sp_addrolemember N'GrantMa', N'ParentA'

  CREATE ROLE [MyRole]

  EXEC sp_addrolemember N'ParentA', N'MyRole'

  CREATE USER [ClarkKent] WITHOUT LOGIN

  EXEC sp_addrolemember N'MyRole', N'ClarkKent'

  EXECUTE AS USER= 'ClarkKent'

  SELECT USER_NAME() --I'm Clark Kent

  --cleanup

  REVERT;

  DROP USER [ClarkKent]

  DROP ROLE [MyRole]

  DROP ROLE [ParentA]

  DROP ROLE [GrantMa]

  DROP ROLE [GrantPa]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  June 29, 2012 at 8:02 am

  #1507138

  i think this is 99% correct

  with x (name,id,gid)

  as

  (

  select a.name,a.principal_id as id,b.role_principal_id as gid from sys.database_principals a inner join sys.database_role_members b on b.member_principal_id=a.principal_id

  where a.principal_id=5

  union all

  select a.name,a.principal_id as id,b.role_principal_id as gid from sys.database_principals a inner join sys.database_role_members b on b.member_principal_id=a.principal_id

  inner join X on x.gid=a.principal_id

  )

  select * from x;

  MVDBA