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/

Björn arbeitet in Hamburg als Datenbank-Administrator und Head of Competence für MS SQL und mySQL. Er nimmt regelmäßig an den PASS Regionalgruppen Treffen in Hamburg, den Veranstaltungen der PASS wie SQLSaturday und SQLGrillen teil und er organisiert in Hamburg das Azure Meetup. Er interessiert sich neben den Themen rund um den SQL Server, Powershell und Azure für Science-Fiction, Snowboarden, Backen 😉 und Radfahren.