Schlagworte: berechtigungen

SSIS 2012 Remote Administrieren

Mit der Version SQL Server 2012 ist einiges sicherer geworden, aber nicht unbedingt einfacher. Wer mit seinem Management Studio remote auf die Integration Services auf einem Server zugreifen möchte und dort kein lokaler Admin ist, muss erst mal einige Hürden überwinden.

Full story »

Umzug in eine neue Domäne

In diesem Artikel werden die Schritte beschrieben, die ich vor und während des Umzugs eines SQL Servers in eine neue Domäne durchgeführt habe. Bei anderen Server sind sicherlich Abweichungen zu beachten.

Full story »

Error: 18456, Severity: 14, State: 11

Auch wenn ich die Ursache dieses Problems noch nicht klären konnte, will ich hier eine kurze Beschreibung geben und eine Lösungsmöglichkeit aufzeigen.
Vor einer Serverumstellung hatte ich schon mal auf dem neuen System einige Testdatenbanken restored und schon ein paar Logins angelegt. Diese Logins hatten die Datenbanken als Default-Datenbank. Während der Umstellung wurden die produktiven Datenbanken (Full + Differential) auf das neue System restored. Die vorher angelegten Logins blieben erhalten. Alle weiteren Logins wurden angelegt.

Nur bei den vorher angelegten Logins bekamen die Anwender Fehlermeldungen:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'Domäne\Konto'. 
Reason: Token-based server access validation failed with an infrastructure error. 
Check for previous errors. 

Bei der Suche nach der Ursache habe ich zuerst die Liste der Status-Codes gefunden, welche sich ohne den Support bearbeiten lassen.
Status 11 sagt also "Valid login but server access failure". Bei der weiteren Suche bin ich dann auf ein SQL gestossen, was mir die Berechtigungen der Logins anzeigt
SELECT t2.name,t1.*
FROM  sys.server_permissions t1 , sys.server_principals t2
WHERE t1.grantee_principal_id = t2.principal_id
AND t1.TYPE<>'R'
ORDER BY name;

Bei der Liste fiel auf, dass für die problembehafteten Logins kein Eintrag vorhanden war. Nun galt es nur noch die fehlende Berechtigung zu setzen:
USE MASTER
GO
GRANT connect sql TO [Domäne\Konto];

und schon konnten die User sich anmelden.

Serverweite Berechtigungen und verwaiste Benutzer

Ausgehend von der Fragestellung, einen Überblick über die Berechtigungen in den verschiedenen Datenbank eines Servers zu erstellen, fand ich die dahinter gelagerten Möglichkeiten auch recht reizvoll.

Nachdem ich bereits vor einigen Wochen ein kleines Skript gepostet habe, welches die Berechtigungen in einer Datenbank darstellt, kam jetzt die Anforderung auf, die Berechtigungen in allen Datenbanken eines ganzen Servers zu dokumentieren. Was lag da näher, als die undokumentierte Prozedur sp_MSforeachdb zu verwenden. Diese Prozedur befindet sich in der master-Datenbank und erwartet im einfachsten Falle einen Befehl, in dem das Fragezeichen als Platzhalter für den Datenbanknamen steht.

Beispielaufruf

exec sp_MSforeachdb @command1='use [?]; select db_name()'
Dieses Skript wechselt also zuerst den Datenbank-Kontext und ruft danach die Funktion zur Ermittlung des Datenbank-Namens auf.

Ermittlung der Berechtigungen

Hier deklariere ich zuerst eine Variable, der ich den Code der Abfrage zuweise und rufe danach die oben beschriebene Prozedur auf. Damit ich die Werte aber dauerhaft habe, lege ich vorher noch eine temporäre Tabelle an, die ich anschliessend für weitere Auswertungen verwenden kann. Hierbei fiel mir auf, dass in einigen Datenbanken WINDOWS_USER existieren, die Spalte Login aber NULL ist. Die Ursache war schnell gefunden: Das Login wurde auf dem Server gelöscht und der Benutzer blieb erhalten. Ähnliches kann auch passieren, wenn man eine Datenbank per Backup/Restore auf einen anderen Server bringt, wo nicht die gleichen Logins angelegt sind. Spontan fielen mir folgende Suchen ein:

  • WINDOWS_USER ohne Login
  • SQLUSER ohne Login
  • DBOs ohne Login

Ich denke, die Möglichkeiten dieser Sammlung werden schnell deutlich. Vielleicht fallen ja dem ein oder anderen noch interessante Ergänzungen ein, die er hier als Kommentar posten möchte.

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.