Listing users and their roles in SQL Server

user_435947 0
Viewed 41236

I want to get a list of all the users in the SQL server database and their roles. What I'm trying to do is to find out if certain users have privileges to more than one database. Is there a query which can do this directly?

sql-server-2005 sql
2 Answers

You can use the below command to find users and corresponding role in each database:

exec sp_MSForeachDB @command1='SELECT db_name(db_id('' ? ''))
    ,user_name(DRM.member_principal_id) [DatabaseUser]
    ,user_name(DRM.role_principal_id) [DatabaseRole]
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP
    ON DRM.member_principal_id = DP.principal_id
INNER JOIN sys.database_principals dpr
    ON drm.role_principal_id = dpr.principal_id
WHERE DRM.member_principal_id > 1
    AND dpr.type IN ('' R '', '' A '')'
user_6349350
Related