« Eindrücke vom SQLSaturday#313
SQL Server AlwaysOn Availability Groups Supported between Microsoft Azure Regions »

Berechtigungen auf Serverebene

Wenn man eine Datenbank auf einen anderen Server überträgt, werden die darin enthaltenen Berechtigungen mitgegeben. Für die Übertragung der Logins (mit Passwort) gibt es verschiedene Möglichkeiten. Was ist aber mit den Berechtigungen auf Serverebene?

Leider gibt es keinen Bericht auf Serverebene, der uns hier einen Überblick geben würde und auch das SQLServer Management Studio bietet diese Informationen nicht auf einen Blick. Man kann sich ein spezielles Login anschauen und dort dann in verschiedenen Registern die Berechtigungen ermitteln, aber einen einfachen Überblick vermissen wohl viele.

Berechtigungen auf Serverrollen

Diese Berechtigungen lassen sich einfach über die Katalogsichten ermitteln, indem man die Principals mit den Rollen verknüpft, für die es ebenfalls eine Zeile in der Sicht der Principals gibt. Serverrollen erkennt man am Type "R". -- Weitere Infos zu Server-Principals unter: Prinzipale (Datenbankmodul)

-- Welche Serverrollen sind vergeben?
SELECT sp.Name AS Principal, sp.Type_Desc AS Principal_Type, sr.name AS Serverrolle
FROM sys.server_principals sp
INNER JOIN sys.server_role_members rm
ON sp.principal_id = rm.member_principal_id
INNER JOIN sys.server_principals sr
ON sr.sid = rm.role_principal_id
WHERE sr.TYPE = 'R'                                           -- Serverrolle
ORDER BY Principal, Serverrolle;

Weitere Berechtigungen auf Serverebene

Jetzt fehlen noch die Berechtigungen, die explizit zugewiesen wurden (wie z. B. VIEW SERVER STATE), also nicht über eine Rolle kommen. Glücklicherweise gibt es auch hierfür eine Katalogsicht, die wir mit den Principals verknüpfen können. Allerweltsrechte wie CONNECT interessieren uns hier nicht. Serverprinzipale, deren Name von doppelten Nummernzeichen (##) eingeschlossen ist, sind nur für die systeminterne Verwendung vorgesehen und werden ebenfalls ignoriert.

-- Welche Nicht-Standard-Berechtigungen sind auf Serverebene vergeben?
SELECT sp.name AS Principal, sp.Type_Desc AS Principal_Type, State_Desc AS Status, p.Permission_Name AS Serverrecht
FROM sys.server_permissions p
INNER JOIN sys.server_principals sp
ON p.grantee_principal_id = sp.principal_id
WHERE p.permission_name NOT IN ('CONNECT SQL', 'CONNECT')   -- Allerweltsrechte interessieren hier nicht
AND sp.name NOT LIKE '##%##'                               -- Serverprinzipale, deren Name von doppelten Nummernzeichen (##) eingeschlossen ist, sind nur für die systeminterne Verwendung vorgesehen.
ORDER BY Principal, Serverrecht;

Benutzerdefinierter Bericht

Damit das ganze etwas handlicher wird, packen wir die Statements einfach in einen Report und verwenden ihn als benutzerdefinierten Bericht auf Serverebene.

Alle Skripte und den Report findet man im Anhang.

  Serverberechtigungen.rdl
  Serverrechte_Ermitteln.sql

administration, berechtigungen, custom, reporting
SQL Server
 Print article This entry was posted by cmu on 26.06.14 at 10:28:00 . Follow any responses to this post through RSS 2.0.