Berechtigungen im SQLServer 2005 anzeigen

Mit Einführung des Management Studios zu SQL Server 2005 sind leider einige Dialoge weggefallen, die die Anzeige der effektiven Berechtigungen einzelner User oder Rollen erleichterten.
(Der Artikel wurde am 05.07.2007 um die Ausgabe des Schema-Namens ergänzt.)

Durch diesen Blog-Eintrag von Jamie Thomson wurde ich inspiriert mich intensiver mit dem Thema zu beschäftigen.

Sicherheits-Katalogsichten

Gleichzeitig mit dem Wegfall alter Dialoge sind aber diverse Sicherheits-Katalogsichten hinzugekommen, die den Zugriff auf diese Informationen ermöglichen. Zur Anzeige der Berechtigungen werden hier die folgenden benötigt:

  • sys.database_permissions
  • sys.database_principals
  • sys.database_role_members
  • sys.server_principals

Ich führe hier teilweise die Beschreibungen aus der Online-Doku ergänzt um eigene Kommentare auf.

sys.database_permissions

Gibt eine Zeile für jede Berechtigung oder Spaltenausnahmeberechtigung in der Datenbank zurück. Für Spalten gibt es eine Zeile für jede Berechtigung, die von der entsprechenden Berechtigung auf Objektebene abweicht. Falls die Spaltenberechtigung mit der entsprechenden Objektberechtigung identisch ist, gibt es dafür keine Zeile und es wird die Objektberechtigung verwendet.

sys.database_principals

Gibt eine Zeile für jeden Prinzipal in einer Datenbank zurück.

Ein Prinzipal ist so etwas wie eine Hauptfigur, wobei in diesem Zusammenhang Datenbank-Benutzer oder -Rollen gemeint sind, die bestimmte Eigenschaften haben können. Diese Eigenschaften sind z. B.:

  • Mitgliedschaft in einer festen Datenbank-Rolle
  • Mitgliedschaft in einer selbstdefinierten Datenbank-Rolle
  • Explizite Berechtigungen

sys.database_role_members

Gibt eine Zeile für jedes Mitglied jeder Datenbankrolle zurück.

Hierüber kann man feststellen, welche Prinzipale (Benutzer und Rollen) Mitglied in einer anderen Rolle sind.

sys.server_principals

Enthält eine Zeile für jeden Prinzipal auf Serverebene.

Auch hier wieder der sehr weiträumige Begriff des Prinzipalen. In diesem Falle verknüpfen wir aber die Datenbank-Prinzipalen mit den Server-Prinzipalen. Dies liefert uns in diesem Fall die Logins zu den Benutzern.

Abfrage der Berechtigungen

Das eigentliche SQL für die Anzeige aller Berechtigungen in einem Result-Set ist etwas komplexer aber durch die Vorweg-Name der Basis-Abfrage in einer Common-Table-Expression nicht zu kompliziert zu lesen.

WITH    perms_cte(principal_name,principal_id, principal_type_desc,class_desc, [object_name], permission_name, permission_state_desc, login ) as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_SCHEMA_NAME(p.major_id) +'.'+OBJECT_NAME(p.major_id) AS [object_name],
                p.permission_name,
                p.state_desc AS permission_state_desc,
                sp.name as login
        from    sys.database_permissions p
        left JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
        left Join sys.server_principals sp   on dp.sid = sp.sid
)
-- users
SELECT p.principal_name,  p.principal_type_desc, login, p.class_desc, p.[object_name], p.permission_name,
p.permission_state_desc, cast('<explizites>' as sysname) as role_name
FROM    perms_cte p
UNION
-- role members
SELECT rm.member_principal_name, rm.principal_type_desc, rm.login, p.class_desc, p.[object_name], p.permission_name,
coalesce(p.permission_state_desc, '<mitglied>'), rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,
    user_name(role_principal_id) as role_name, sp.name as login
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp ON     rm.member_principal_id = dp.principal_id
    left    Join sys.server_principals sp on dp.sid = sp.sid
) rm
ON     rm.role_principal_id = p.principal_id
order by 2, 1, 4, 5, 6, 8

Teilt man das ganze nun auf, um einen benutzerdefinierten Bericht für Rollen und Benutzer zu erzeugen, fallen zwei verschiedene aber sehr ähnliche SQLs an. Diese unterscheiden sich durch die Abfrage der Rollen:

WHERE   coalesce(principal_type_desc,'NULL') = 'DATABASE_ROLE'

bzw. auf Ungleichheit, wenn es um die Benutzer geht.

Das ganze kann man nun für jede SQL Server 2005-Datenbank aufrufen, wenn man die Funktionalität der benutzerdefinierten Berichte ausnutzt. Ein einfacher Beispielbericht ist dem Beitrag angehängt.

  Serverberechtigungen per Report anzeigen mit "Ziel speichern unter" als RDL-File abspeichern