sql-serverssmsroles

How to find which Database Roles are associated with a given User?


In SSMS I have a User X and there are Y, Z and P Database Roles available, how may I check what roles are added to a user X?

What have I tried:

In SSMS right click on database -> properties -> Permissions and see Explicit tab for a user X. I can see Permissions not association between role and the user. The same is for role I'm interested in, I see only permissions for role.

EDIT: Regarding GUI solution, I have no Properties option available for Users nor Roles.

enter image description here


Solution

  • You can use the sys.database_principals object to find this out:

    SELECT u.[name] AS [UserName],
           r.[name] AS RoleName 
    FROM sys.database_principals u
         JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id
         JOIN sys.database_principals r ON  drm.role_principal_id = r.principal_id
    WHERE u.[type] IN ('S','U') --SQL User or Windows User
      AND u.[name] = N'X';