Listing users and their roles in SQL Server

Question

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?

Sitter

Answer 1

I think you'll find this resource helpful:

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions.aspx

From the article:

select dp.NAME AS principal_name,
       dp.type_desc AS principal_type_desc,
       o.NAME AS object_name,
       p.permission_name,
       p.state_desc AS permission_state_desc
from   sys.database_permissions p
left   OUTER JOIN sys.all_objects o
on     p.major_id = o.OBJECT_ID
inner  JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id

Mogerly

Answer 2

If you just want to get a list of users and role assignments for one db, you can do this. This will work on Azure SQL.

select P.Name, R.Name
from sys.database_principals P 
left outer join sys.database_role_members RM on P.principal_id=RM.member_principal_id 
left outer join sys.database_principals R on R.principal_id=RM.role_principal_id

If you are looking for the server roles use this:

select
    u.name [user],
    r.name [role]
from 
    sys.server_principals u
    join sys.server_role_members rm 
        on u.principal_id = rm.member_principal_id
        and u.type in ('S')
    join sys.server_principals r 
        on r.principal_id = rm.role_principal_id
        and r.type in ('R')

Deluxe

Answer 3

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

Decided

Answer 4

consolidated output for the previous query.

    drop table if exists #temp
create table #temp(dbname varchar(50),DatabaseUser varchar(100),DatabaseRole varchar(100))
exec sp_msforeachdb @command1='insert into #temp SELECT db_name(db_id(''?''))dbname
    ,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'')'
select * from #temp

Gelation