1

I need to display the list of all the roles a login is associated with. The logins are SQL Server logins. I tried all three:

Execute sp_helpuser 'example'

and

SELECT 
MP.name, RP.name
FROM 
sys.database_role_members RM
JOIN sys.database_principals RP ON RM.role_principal_id = RP.principal_id
JOIN sys.database_principals MP ON RM.member_principal_id = MP.principal_id
where MP.name = 'example'

and

select
convert(char(45),sp.name) as srvLogin,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from 
 sys.server_principals as sp join
 sys.database_principals as dbp on sp.sid=dbp.sid join
 sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join
 sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join 
 sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
 sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id
 where sp.name='example'

All of these gave me 10 roles out of 23 total roles.

Is there any other query that will list all the roles. I am not sure if there are any permission that needs to be set.

I ran this query as sysadmin and also as a user associated with one of the roles. All the users are member of Public role.

  • You haven't included server_role_members there for server roles. And you've also limited yourself to a single database, rather than all databases on the server. – Nic Oct 12 '16 at 16:35
  • Thank you for the quick update. I modified the question to include the server_role_members query that I used. All of the 23 roles are on the same Database and there is only one Database on the server. – Thisisme.. Oct 12 '16 at 17:32
  • is the user directly a member of the roles or is there nested membership? is user member of role, which is member of another role or windows account is member of some roles and windows group is member of other roles – Bob Klimes Oct 12 '16 at 18:16
  • These are all SQL Login Accounts and was added to multiple roles to access multiple modules based on requirement. All user are directly added to the roles. Roles are created on Database level and users are assigned directly to that role. there are no nested membership. – Thisisme.. Oct 12 '16 at 18:30
  • do you see all the roles when you run this: SELECT * FROM sys.database_principals where type = 'R' – Bob Klimes Oct 12 '16 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.