Welcher SQL Benutzer darf was auf meine SQL Server – Rollen-/Rechte Analyse

Mal wieder eine Anfrage von unseren Kunden-Betreuern erhalten, wir sollen mal wieder eine Auswertung liefern…

Es gibt Situationen in denen wir oder auch der Kunde gerne eine Übersicht über alle eingerichteten SQL Benutzer und deren Berechtigungen auf einem SQL Server wünscht bzw wir bei der Übernahme von neuen Servern nicht nur auf die Skripte von Brent Ozar zurück greifen und die obige Anfrage des öfteren kommt, haben wir uns ein entsprechenden Statement gebaut.

Anforderung:

• alle eingerichteten SQL Benutzer sollen gelistet werden (keine Systemuser)
• Zuordnung soll ersichtlich sein (SQL Benutzer, Windows User oder Windows Gruppe)
• Welcher Serverrolle wurde dem jeweiligen User zugeteilt?
• Wann wurde der Account anlegt bzw das letzte mal aktualisiert?

Für solche Auswertungen eignen sich die systemeigenen DMVs wunderbar, in diesem Fall sind es die Views:

• sys.server_principals
• sys.syslogins

Die Auswahl der sinnvoll darstellbaren Spalten in der Auswertung – damit der Kunde und der Kundenbetreuer es auch „versteht“ – und eine sprechende Benamung der einzelnen Spalten ergeben dann das folgende Skript. (ich habe das Rad nicht neu erfunden, nur für mich erweitert 😉 )

USE master
GO
 
SELECT  p.name AS [loginname] ,
        p.type_desc ,
        p.is_disabled,
        s.sysadmin as IsSysAdmin ,
        s.serveradmin as IsServerAdmin,
        s.securityadmin as IsSecurityAdmin ,
        s.processadmin as IsProcessAdmin,
        s.setupadmin as IsSetupAdmin,
        s.bulkadmin as IsBulkAdmin,
        s.diskadmin as IsDiskAdmin,
        s.dbcreator as IsDBCreator,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        and p.name not like 'xyz\accountname' -- put your admin-accounts in here

Das TSQL Statement filtert das Resultset nach reinen SQL Benutzern und Windows-Usern sowie Windows Gruppen (sowohl lokalen als auch Domänen), mittels der Einschränkung ‚##%‘ grenzen wir auch (für diese Auswertung) relevante System-Benutzer aus.
Da allen Empfängern dieser Auswertung bewußt ist, dass wir als DBAs immer volle Rechte (sysadmin-Rolle) haben, filtern wir diese Benutzer(oder Benutzergruppen) heraus => and p.name not like ‚xyz\accountname‘

Als Ergebnis erhalten wir dann folgende Tabelle, die uns übersichtlich darstellt, welcher Account über welche Rolle verfügt:

loginname	type_desc	is_disabled	IsSysAdmin	IsServerAdmin	IsSecurityAdmin	IsProcessAdmin	IsSetupAdmin	IsBulkAdmin	IsDiskAdmin	IsDBCreator	created	update
sa	SQL_LOGIN	0	1	0	0	0	0	0	0	0	04.08.2003	10.08.2013
NT SERVICE\SQLWriter	WINDOWS_LOGIN	0	1	0	0	0	0	0	0	0	10.09.2012	10.09.2012
NT SERVICE\Winmgmt	WINDOWS_LOGIN	0	1	0	0	0	0	0	0	0	10.09.2012	10.09.2012
NT SERVICE\MSSQLSERVER	WINDOWS_LOGIN	0	1	0	0	0	0	0	0	0	10.09.2012	10.09.2012
NT SERVICE\ClusSvc	WINDOWS_LOGIN	0	0	0	0	0	0	0	0	0	10.09.2012	10.09.2012
NT AUTHORITY\SYSTEM	WINDOWS_LOGIN	0	0	0	0	0	0	0	0	0	10.09.2012	10.09.2012
NT SERVICE\SQLSERVERAGENT	WINDOWS_LOGIN	0	1	0	0	0	0	0	0	0	10.09.2012	10.09.2012
EP\sql_serv	WINDOWS_LOGIN	0	1	0	0	0	0	0	0	0	10.09.2012	10.09.2012
Attunity	SQL_LOGIN	0	0	0	0	0	0	0	0	0	10/16/2012	12/23/2012
BackupHist	SQL_LOGIN	0	0	0	0	0	0	0	0	0	11/15/2013	11/15/2013
domain\accountname	WINDOWS_LOGIN	0	1	0	0	0	0	0	0	0	11/15/2013	11/15/2013
domain\groupname	WINDOWS_GROUP	0	1	0	0	0	0	0	0	0	11/15/2013	11/15/2013

Wenn man diese Tabelle nun in ein Excel-Sheet kopiert, kann man die relevanten Zeilen optisch besser markieren, so dass die Empfänger schneller und einfacher eine Übersicht erhalten.
Gemäß unserer Security Baselines darf eine Kunden-User maximal die Server-Rolle „dbcreator“ haben, daher könnte man die SQL Benutzer oder Gruppen farbig (rot) markieren, wenn diese über zuviele Rechte verfügen. Oder wenn man erkennt, dass der SQL Benutzer „sa“ nicht wie empfohlen disabled wurde.

Nun kann man die Auswertung entweder einfach als Anhang oder per Copy/Paste in eine Mail einfügen und den jeweiligen Empfängern zur Verfügung stellen.

Wer diese Auswertung ggfs in regelmäßigem Abständen haben will, kann sich dies auch als SQL Agent Job hinbauen, Ausgabe der Tabelle als HTML Konstrukt und den HTML-Output gleich per SQLMail versenden.

Hinweise, wie man solche Mails bzw Jobs erstellt und entsprechend formatiert, findet man im SQLServerCentral unter: http://www.sqlservercentral.com/articles/T-SQL/99398/