MS SQL server request to withdraw all users

Vladimir 21

Hello everyone!
Please help me make a request in MS SQL Server
It is necessary to display all accounts, sql and domain, which will display the fields account name, conestion string, database name, Usermapingdatabase (screenshot 2) and server role (screenshot 3)
an example of how it should look for one account (screenshot1)

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2022-01-22T10:57:27.55+00:00

Please post the output of "SELECT @@version" so we what your presumptions are.
LiHong-MSFT 10,056 Reputation points

2022-01-26T09:37:44.657+00:00

Hi,@Vladimir
Have your issue resolved?If not , please feel free to share with us with more details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!

Best regards,
LiHong

7 answers

Tom Phillips 17,771

See:
https://www.mssqltips.com/sqlservertip/5999/sql-server-database-users-to-roles-mapping-report/
and
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-role-members-transact-sql?view=sql-server-ver15#_examples

Vladimir 21

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-role-members-transact-sql?view=sql-server-ver15#_examples

SELECT	roles.principal_id							AS RolePrincipalID  
	,	roles.name									AS RolePrincipalName  
	,	server_role_members.member_principal_id		AS MemberPrincipalID  
	,	members.name								AS MemberPrincipalName  
FROM sys.server_role_members AS server_role_members  
INNER JOIN sys.server_principals AS roles  
    ON server_role_members.role_principal_id = roles.principal_id  
INNER JOIN sys.server_principals AS members   
    ON server_role_members.member_principal_id = members.principal_id

;
How to add role public to this request ? tell me please

Tom Phillips 17,771 Reputation points

2022-01-31T15:32:30.033+00:00

All users are members of "public". There is no way to remove users from public.
Vladimir 21 Reputation points

2022-01-31T15:38:48.21+00:00

The problem is, if there are users who have only the role of public, then they are simply not displayed by this request. And they need to be taken out.
Vladimir 21 Reputation points

2022-01-31T15:41:08.127+00:00

How to add a public role column? to this request?

Tom Phillips 17,771

Try this

 SELECT    roles.principal_id                            AS RolePrincipalID
     ,    roles.name                                    AS RolePrincipalName
     ,    server_role_members.member_principal_id        AS MemberPrincipalID
     ,    members.name                                AS MemberPrincipalName
 FROM sys.server_role_members AS server_role_members
 LEFT OUTER JOIN sys.server_principals AS roles
     ON server_role_members.role_principal_id = roles.principal_id
 LEFT OUTER JOIN sys.server_principals AS members 
     ON server_role_members.member_principal_id = members.principal_id  
UNION ALL
 SELECT    roles.principal_id                            AS RolePrincipalID
     ,    roles.name                                    AS RolePrincipalName
     ,    NULL        AS MemberPrincipalID
     ,    members.name                                AS MemberPrincipalName
from sys.server_principals AS members 
    CROSS APPLY sys.server_principals AS roles
WHERE roles.[type] = 'R' and roles.[name] = 'public'
    AND members.[type] IN ('S','U')

Vladimir 21

It turned out to be done like this:

select DB_NAME() as db, u.name as [login], r.name as [role], u.[sid] 
into ##db_users_roles01
from
sys.database_principals u
left join sys.database_role_members m on u.principal_id = m.member_principal_id
left join sys.database_principals r on r.principal_id = m.role_principal_id
where u.type in ('S', 'U') and 1=2
exec sp_msforeachdb 'use [?]; 
insert into ##db_users_roles01
select DB_NAME() as db, u.name as [login], r.name as [role], u.[sid] from
sys.database_principals u
left join sys.database_role_members m on u.principal_id = m.member_principal_id
left join sys.database_principals r on r.principal_id = m.role_principal_id
where u.type in (''S'', ''U'')'
select *, 1 as [public] from (
select sp.name as serverLoginName, Isnull(srp.name,'public') as ServerRoleName,db,role,1 as value
From sys.server_principals  sp
left join sys.server_role_members srm on sp.principal_id = srm.member_principal_id 
left join sys.server_principals srp on srp.principal_id = srm.role_principal_id
left join ##db_users_roles01 dbr on dbr.sid = sp.sid
where sp.type in ('S', 'U')
)sub
Pivot( Sum(value) for role in (
db_accessadmin      ,
db_backupoperator    ,
db_datareader      ,
db_datawriter      ,
db_ddladmin        ,
db_denydatareader    ,
db_denydatawriter    ,
db_owner             ,
db_securityadmin     ,
SCOM_HealthService
)) as piv

Share via

MS SQL server request to withdraw all users

7 answers

Your answer