Archiv für Juni 2009

Nachdem ich schon seit ein paar Wochen nichts geschrieben habe, wird es mal wieder Zeit.

Vor einigen Monaten haben wir auf der Arbeit unsere altes Cluster neu aufgesetzt, und den SQL Server auf neuer Hardware installiert. Nachdem wir massig SQL Logins und Windows Gruppen berechtigt hatten, stellte sich die Frage, wie wir diese Logins wieder auf den Datenbanken berechtigen können, ohne uns durch jede Datenbank zu klickern.

Um die Logins anzulegen hatte ich bereits ein Script zur Hand, jedoch mußten die neuen Logins (neue SID) mit den Datenbanken synchronisiert werden. Hier habe ich auf der SQLServerCenteral zwei kleine aber sehr gute Helferscripte gefunden, welche jedem DBA das Leben erleichtern.

Zunächst das erste Script, welches alle nicht synchronen Logins auf dem Server ausliest:

DECLARE @Collation varchar(100)
DECLARE @SQL VARCHAR(2000)

CREATE TABLE ##TempSync
(
DB_NME Varchar(50),
DBUserName varchar(50),
SysLoginName varchar(50)
)

SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?]
SELECT ''?'' DB_NME,
       A.name DBUserName,
       B.loginname SysLoginName
 FROM sysusers A
      JOIN master.dbo.syslogins B
      ON A.name Collate ' + @Collation + ' = B.Name
      JOIN master.dbo.sysdatabases C
      ON C.Name = ''?''
 WHERE issqluser = 1
       AND (A.sid IS NOT NULL
       AND A.sid <> 0x0)
       AND suser_sname(A.sid) IS NULL
       AND (C.status & 32) =0 --loading
       AND (C.status & 64) =0 --pre recovery
       AND (C.status & 128) =0 --recovering
       AND (C.status & 256) =0 --not recovered
       AND (C.status & 512) =0 --offline
       AND (C.status & 1024) =0 --read only
 ORDER BY A.name'

INSERT into ##TempSync
EXEC sp_msforeachdb @SQL

SELECT * FROM ##TempSync

DROP TABLE ##TempSync

Und nun das Script, was alle Logins wieder synchronisiert

DECLARE @Collation VARCHAR (100)
DECLARE @SQL VARCHAR(2000)

SELECT @Collation =CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?]
DECLARE @DBUserName varchar(50)
DECLARE @SysLoginName varchar(50)
DECLARE SyncDBLogins CURSOR FOR
 SELECT A.name DBUserName,
        B.loginname SysLoginName
 FROM sysusers A
      JOIN master.dbo.syslogins B
      ON A.name Collate ' + @Collation + ' = B.Name
      JOIN master.dbo.sysdatabases C
      ON C.Name = ''?''
 WHERE issqluser = 1
       AND (A.sid IS NOT NULL
       AND A.sid <> 0x0)
       AND suser_sname(A.sid) IS NULL
       AND (C.status & 32) =0 --Loading
       AND (C.status & 64) =0 --pre recovery
       AND (C.status & 128) =0 --recovering
       AND (C.status & 256) =0 --not recovered
       AND (C.status & 512) =0 --offline
       AND (C.status & 1024) =0 --read only
 ORDER BY A.name

OPEN SyncDBLogins
FETCH NEXT FROM SyncDBLogins
 INTO @DBUserName, @SysLoginName

WHILE @@FETCH_STATUS = 0
 BEGIN
    EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName

    FETCH NEXT FROM SyncDBLogins
    INTO @DBUserName, @SysLoginName
 END
CLOSE SyncDBLogins
DEALLOCATE SyncDBLogins
'
EXEC sp_msforeachdb @SQL

Orginal Artikel: http://go.xinox.net/52sceq