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