Schnelle Übersicht über Serverrollen und deren Mitglieder

Details
Kategorie: T-SQL 
---Listet alle Logins mit Sererroles

DECLARE	@stmt	nvarchar(255)
; with cte as(
SELECT *
FROM
(
	SELECT	
	        @@servername [ServerName],  
	        r.name	AS	[RoleName],
			m.name	AS	[MemberName],
			CASE WHEN m.name IS NOT NULL
				 THEN 'X'
				 ELSE NULL
			END		AS IsMember
	FROM	sys.server_principals r LEFT JOIN sys.server_role_members rm
			ON	(r.principal_id = rm.role_principal_id) LEFT JOIN sys.server_principals m
			ON	(rm.member_principal_id = m.principal_id)
	WHERE	r.type = 'R'
) p
PIVOT
(
	MAX (IsMember)
	FOR	[RoleName] IN ([public], [sysadmin], [securityadmin], [serveradmin], [setupadmin], [processadmin], [diskadmin], [dbcreator], [bulkadmin])
) AS pvt
WHERE MemberName IS NOT NULL
)

select rank()  over( Order By [MemberName]) as No,
[ServerName]
,[MemberName]
,COALESCE ([public],'') as [public]
,COALESCE ([sysadmin],'') as [sysadmin]
,COALESCE ([securityadmin],'') as [securityadmin]
,COALESCE ([serveradmin],'') as [serveradmin]
,COALESCE ([setupadmin],'') as [setupadmin]
,COALESCE ([processadmin],'') as [processadmin]
,COALESCE ([diskadmin],'') as [diskadmin]
,COALESCE ([dbcreator],'') as [dbcreator]
,COALESCE ([bulkadmin],'') as [bulkadmin]


 from cte

Main Menu