How to find all logins and their server role memberships?

Have you ever wished to know or have you ever been requested to create a report to show what logins belong to what server roles? While sys.server_role_members alone can produce the logins and server roles by supplementing it using the system function SUSER_NAME() as in:

SELECT SUSER_NAME(sr.role_principal_id) AS [Server Role]
		, SUSER_NAME(sr.member_principal_id) AS [Login Name]
	FROM sys.server_role_members sr

If you need to see other user data columns such as is_disabled, create_date, modify_date, and default_database_name of the logins, you will need to join sys.server_role_members to sys.server_principals. The following is one way that this can be done:

-- find all logins and their server role memberships
SELECT sp.name AS [Login Name],
	sp.type_desc [Account Type],
	sp.is_disabled,
	rp.name AS [Server Role],
    CONVERT(VARCHAR(10),sp.create_date ,101) AS [Date Created],
    CONVERT(VARCHAR(10),sp.modify_date , 101) AS [Date Last Modified],
	sp.default_database_name AS [Default Database]
FROM sys.server_role_members rm JOIN sys.server_principals rp ON rm.role_principal_id = rp.principal_id
	JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id
WHERE sp.type in ('S','U','G')
Be Sociable, Share!
You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.