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'
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'
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.