Problem
Wer hat das nicht schon einmal erlebt – es kommt eine Anfrage, dass eine neue Maschine mit einer SQL Server – Instanz für produktive oder Test – Zwecke installiert wurde und da soll bitte schön ein User „genau so wie in der Produktion“ angelegt werden.
„Peanuts!“, habe ich mir gedacht, denn das SQL Server 2005 Management Studio erstellt doch gerne Skripte für alles, was sich in dem Server befindet.
Leider Fehlanzeige – wenn ich auf einen DB-User klicke und „Script User as… / CREATE“ wähle, dann erhalte ich lediglich einen einzigen „CREATE USER“ – Befehl als Ausgabe. Alle Zugehörigkeiten zu den Datenbankgruppen und insbesondere die direkt vergebenen Zugriffsrechte auf Datenbankobjekte, die recht zahlreich und kompliziert sein können, sind in dem generierten Skript nicht vorhanden.
Noch schlimmer ist es, wenn ein Login in mehreren Datenbanken über einen User verfügt, dann müssen die Zugehörigkeiten / Rechte in jeder betroffenen DB mühsam manuell zusammengetragen werden.
Lösung
Da habe ich mich natürlich gefragt, ob so etwas nicht programmatisch möglich wäre? Das Programm sollte natürlich nicht nur mit SQL Server 2005, sondern auch mit SQL 2000, und möglicherweise SQL 7, funktionieren, denn im Moment muss betreue ich eine Vielzahl von SQL Servern, hauptsächlich 2000er Version.
Das Ergebnis dieser Überlegungen war ein T-SQL - Skript mit etwas mehr als 200 Zeilen Code. In das Skript muss nur am Anfang der DB-User-Name eingetragen werden. Dann führt man das Skript in der gewünschten Datenbank aus und bekommt als Ausgabe einen hübsch formatierten T-SQL-Batch die Erstellung des Benutzers, zum Beispiel:
use [MyDB]
execute sp_grantdbaccess 'DOMAIN\MuserDomainUser', 'MuserUser'
execute sp_addrolemember 'db_datareader', 'MuserUser'
grant SELECT, INSERT, UPDATE, DELETE on [TABELLE1] to [MuserUser]
grant SELECT, INSERT, UPDATE, DELETE on [TABELLE2] to [MuserUser]
grant SELECT, INSERT on [TABELLE3] to [MuserUser]
grant EXECUTE on [sp_proc1] to [MuserUser]
grant EXECUTE on [sp_proc2] to [MuserUser]
Wenn das Login in mehreren Datenbanken über einen User verfügt, muss ich dann ggf. mein Skript in jeder betroffenen DB starten und die Ausgaben zu einem „Erstellungsskipt“ zusammenführen. Auf dem Zielsystem muss nur das entsprechende Login vorhanden sein, und es reicht dann, das „Erstellungsskript“ dort auszuführen, um die gleichen DB-Mappings und -Berechtigungen für das betroffene Login zu bekommen wie auf dem Quellsystem.
Diese Technologie kann auch dazu verwendet werden, die Berechtigungen in Systemen zu dokumentieren oder durch eine „Archiv – Kopie“ den Ist – Status der Berechtigungen vor einem Update etc. festzuhalten.
Skripte
Leider hat es aber nicht geklappt, ein wirklich universelles Skript für alle gängigen Versionen des SQL Servers zu entwickeln. Das Problem liegt daran, dass der Besitzer eines Objektes in SQL Server ab Version 2005 nicht mehr ein Benutzer, sondern von ein Schema ist, und so ist in einer Abfrage eine Verknüpfung zu „sys.schemas“ anstatt „sysusers“ erforderlich. Daher findet man im Anhang an diesen Beitrag zwei Skripte:
Für Testzwecke habe ich ebenfalls ein Skript erstellt, TestScript.sql. Es erstellt einige Objekte in der TEMPDB – Datenbank, ein Login und daraus einen User in TEMPDB, dem dann die Berechtigungen an die gerade erstellten Objekte vergeben werden. Danach kann man z. B. script_user_with_all_rights.sql ausführen und prüfen, ob das Skript für die Berechtigungen korrekt erstellt wird.
Einschränkungen
Das Programm wurde konzipiert, um in der täglichen Arbeit zu helfen und die mehr oder weniger typischen Aufgaben zu erledigen; daher gibt es einige Punkte, die für mich irrelevant waren und von dem Skript dementsprechend nicht bearbeitet werden – ich werde mich aber selbstverständlich freuen, wenn jemand dies nachrüsten und mir anschließen die aktualisierten Skripte – z. B. durch Kommentare an diesen Beitrag oder per E-Mail – zur Verfügung stellen würde! Also:
-
Berechtigungen auf Column – Ebene werden nicht berücksichtigt.
-
Berechtigungen auf Schemas in SQL Server ab 2005 werden nicht berücksichtigt.
-
Es gibt Ausführungsprobleme, wenn die Datenbank, in der Skript ausgeführt wird, eine andere Collation hat als der Server (also sprich andere Collation als TEMPDB).
Anhänge
Die Anhänge zu diesem Beitrag können hier heruntergeladen werden.