How to List the Server Roles for the Logins in a Row Separated by a Delimiter?

You have met the challenge to create the report to show all the logins and their corresponding servers roles. Now, some very wise people with pay grades much higher than yours have requested that you need to format the report so that the logins belonging to multiple server roles will be listed in one row rather than separate rows for server roles. For example, for demonstration purposes, I have added the TestLogin to both the sysadmin and the setupadmin server roles. Without added formatting, these two server roles will show in individual rows for the TestLogin. Then, I formatted the report so that the TestLogin will have both the sysadmin and setupadmin server roles listed in one row rather than the previous individual row for each. Here’s the screen-shot of the explanation and end result:
Formatting the Server Roles into a Row

The correlated subquery and the STUFF() function did all the magic. Here we go with the script:

-- find all logins and their server role memberships
IF OBJECT_ID('tempdb..#Logins_SrvRoles') IS NOT NULL
	DROP TABLE #Logins_SrvRoles;	

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]
INTO #Logins_SrvRoles
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')

-- formatting the server roles for the logins into a row
SELECT L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database],
	STUFF((SELECT '; ' + LR.[Server Role]
		FROM #Logins_SrvRoles LR
		WHERE LR.[Login Name] = L.[Login Name]
		FOR XML PATH('')),1,1,''
	) [SRV_Roles]
FROM #Logins_SrvRoles L
GROUP BY L.[Login Name], L.[Account Type], L.is_disabled, L.[Date Created], L.[Date Last Modified], L.[Default Database]
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.