Listing all the SQL Server Logins and the Server Roles they belong to

On 17 Apr, 2015

MS SQL DBA

By : Charith Silva

No Comments

Below script will help you to extract a list of all the SQL Server Logins and a comma-separated list of the Server Roles they belong to.

USE [master];
GO

SELECT DISTINCT 
rm.member_principal_id AS [Principal ID], 
m.name AS [Principal Name],
m.type_desc AS [Principal Type],
STUFF((
SELECT ',' + r_sub.name
FROM sys.server_role_members rm_sub
INNER JOIN sys.server_principals AS r_sub ON rm_sub.role_principal_id = r_sub.principal_id
INNER JOIN sys.server_principals AS m_sub ON rm_sub.member_principal_id = m_sub.principal_id
WHERE rm.member_principal_id=rm_sub.member_principal_id
FOR XML PATH ('')

),1,1,'') AS [Role Name],
m.default_database_name AS [Default Database],
r.create_date AS [Create Date],
m.modify_date AS [Modify Date]
FROM sys.server_role_members rm
INNER JOIN sys.server_principals AS r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals AS m ON rm.member_principal_id = m.principal_id
ORDER BY m.name

USE [master];

SELECT DISTINCT

rm.member_principal_id AS [Principal ID],

m.name AS [Principal Name],

m.type_desc AS [Principal Type],

STUFF((

SELECT ',' + r_sub.name

FROM sys.server_role_members rm_sub

INNER JOIN sys.server_principals AS r_sub ON rm_sub.role_principal_id = r_sub.principal_id

INNER JOIN sys.server_principals AS m_sub ON rm_sub.member_principal_id = m_sub.principal_id

WHERE rm.member_principal_id=rm_sub.member_principal_id

FOR XML PATH ('')

),1,1,'') AS [Role Name],

m.default_database_name AS [Default Database],

r.create_date AS [Create Date],

m.modify_date AS [Modify Date]

FROM sys.server_role_members rm

INNER JOIN sys.server_principals AS r ON rm.role_principal_id = r.principal_id

INNER JOIN sys.server_principals AS m ON rm.member_principal_id = m.principal_id

ORDER BY m.name

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva