Server-Logins für DB-Benutzer erzeugen

[as2000]

Hi All,

ich möchte gerne eine bestehende SQL-Server Datenbank eines Produktivsystems regelmäßig auf eine Entwicklungsumgebung per Backup und Restore übertragen. Dazu habe ich nun einen SQL-Server für Testzwecke installiert, der nun völlig jungfräulich dasteht. Beim Wiederherstellen (Restore) einer zuvor per Backup auf dem Produktivsystem gezogenen Sicherung erhalte ich die vollständige Datenbank incl. deren Datenbankbenutzern. Jedoch haben diese Datenbankbenutzer keine Benutzernamen bzw. Logins, da diese ja im SQL-Serversystem noch gar nicht existieren.
Wie kann ich nun aus den vorhandenen Datenbankbenutzern der per Restore hergestellten Datenbank Logins für das SQL-Serversystem erzeugen?

ciao Andi

[Andreas Kosch]

Hallo,

Wie kann ich nun aus den vorhandenen Datenbankbenutzern der per Restore hergestellten Datenbank Logins für das SQL-Serversystem erzeugen?

Wenn der Entwicklungsrechner ein Backup eines anderen Servers einbinden soll, ohne das jedes Mal die Systemprozedur sp_change_users_login notwendig wird, muss auf der SQL Server-Instanz des Entwicklungsrechners ein SQL Server-Login angelegt werden, das die gleiche SID (security identifier) wie beim Quellserver verwendet. Bei der CREATE LOGIN-Anweisung muss in diesem Fall die SID-Option verwendet werden. Damit das Ganze nicht mühsam von Hand ermittelt werden muss, ist es am Besten, den Inhalt des SQL-Scripts einmalig auf dem Quellserver zusammenzustellen:

SELECT 
  'create login [' + p.name + '] ' + 
  CASE 
    WHEN p.type in('U','G') THEN 'from windows ' ELSE '' 
  END + 'with ' +
  CASE 
    WHEN p.type = 'S' THEN 'password = ' + 
      master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' + 'sid = ' + 
      master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' +
      CASE 
         WHEN l.is_policy_checked > 0 THEN 'ON, ' ELSE 'OFF, ' 
      END + 'check_policy = ' + 
      CASE 
        WHEN l.is_expiration_checked > 0 THEN 'ON, ' ELSE 'OFF, ' 
      END +
      CASE 
        WHEN l.credential_id > 0 THEN 'credential = ' + c.name + ', ' ELSE '' 
      END 
    ELSE '' 
  END + 'default_database = ' + p.default_database_name +
  CASE 
    WHEN len(p.default_language_name) > 0 THEN ', default_language = ' + 
      p.default_language_name ELSE '' 
  END
FROM sys.server_principals p
  LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
  LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id
WHERE 
  p.type in('S','U','G') and p.name <> 'sa';

Wenn dieses erzeugte SQL-Script dann auf dem Entwicklungsrechner eingespielt wird, sollte sich auch das wiederhergestellte Backup sofort verwenden lassen.

[as2000]

Hallo Andreas,

vielen Dank für Deine Antwort. Leider hatte ich vergessen mitzuteilen, dass ich unter SQL-Server 2000 arbeite und nun Fehlermeldungen für sys.server_principals, sys.sql_logins und sys.credentials bekomme. Wie muss ich die drei Zeilen für 2000 umschreiben?
Wie würde das Ganze denn per Hand aussehen - ich hab hier nur zwei DB-Benutzer, die ich abbilden muss?

ciao Andi

[Andreas Kosch]

Hallo,

..dass ich unter SQL-Server 2000 arbeite ...

wenn eine Frage im Forum-Ordner für den MS SQL Server 2005 gestellt wird, muss man in der Tat dies bereits bei der Formulierung der Frage sehr deutlich herausstellen ;-)

Das Gegenstück zur CREATE LOGIN-Anweisung vom MS SQL Server 2005 ist beim 2000er SQL Server die Systemprozedur sp_addlogin. Wenn es nur um 2 Benutzer geht, lohnt sich der Aufwand für das Generieren des Scriptes nicht (das Script verzeigt über CASE, um die integrierten Windows-Authentifizierung vom klassischen SQL Server-Login über eine Benutzername/Passwort-Kombination unterscheiden zu können). Die Fassung für den MS SQL Server 2000 könnte so aussehen

sp_addlogin 'Benutzername', 'Benutzerpasswort', 'Standarddatenbank'
GO
sp_grantdbaccess 'Datenbank'
GO

[hwoess]

Hallo Andreas,

das Script war nicht umsonst, ich hatte gerade ein ähnliches Problem wie Andy, allerdings für den 2005er, deine Infos waren genau das, was ich gesucht habe

vielen Dank,
Helmut