En reliant TOUS les Utilisateurs de se connecter après la restauration d'une base de données SQL Server 2005

Je suis venu avec la suivante. Il fonctionne très bien car il vous montre:

1. Tous les utilisateurs orphelins.
2. Ceux qui ont été fixés.
3. Ceux qui ne pouvaient pas être fixés.

D'autres solutions nécessitent que vous savez les orphelins nom d'utilisateur avant la main afin de les corriger.

Le code suivant pourrait exécuter une procédure stockée qui est appelée après la restauration d'une base de données vers un autre serveur.

Script:

EXEC sp_change_users_login 'report'--See all orphaned users in the database.
DECLARE @OrphanedUsers TABLE
(
IndexKey Int IDENTITY(1,1) PRIMARY KEY,
UserName SysName,--nVarChar(128)
UserSID  VarBinary(85)
)
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'
DECLARE @CRLF as nVarChar
SET @CRLF = CHAR(10) + '&' + CHAR(13)--NOTE: Carriage-Return/Line-Feed will only appear in PRINT statements, not SELECT statements.
DECLARE @Sql as nVarChar(MAX)
SET @Sql = N''
DECLARE @IndexKey as Int
SET @IndexKey = 1
DECLARE @MaxIndexKey as Int
SET @MaxIndexKey = (SELECT COUNT(*) FROM @OrphanedUsers)
DECLARE @Count as Int
SET @Count = 0
DECLARE @UsersFixed as nVarChar(MAX)
SET @UsersFixed = N''
DECLARE @UserName as SysName--This is an orphaned Database user.
WHILE (@IndexKey <= @MaxIndexKey)
BEGIN
SET @UserName = (SELECT UserName FROM @OrphanedUsers WHERE IndexKey = @IndexKey)
IF 1 = (SELECT COUNT(*) FROM sys.server_principals WHERE Name = @UserName)--Look for a match in the Server Logins.
BEGIN
SET @Sql = @Sql + 'EXEC sp_change_users_login ''update_one'', [' + @UserName + '], [' + @UserName + ']' + @CRLF
SET @UsersFixed = @UsersFixed + @UserName + ', '
SET @Count = @Count + 1
END
SET @IndexKey = @IndexKey + 1
END
PRINT @Sql
EXEC sp_executesql @Sql
PRINT   'Total fixed: ' + CAST(@Count as VarChar) + '.  Users Fixed: ' + @UsersFixed
SELECT ('Total fixed: ' + CAST(@Count as VarChar) + '.  Users Fixed: ' + @UsersFixed)[Fixed]
EXEC sp_change_users_login 'report'--See all orphaned users still in the database.

Résultat:

*Remarque: Les 4 qui n'ont pas été corrigés (dans mon exemple, la capture d'écran ci-dessus) n'ont pas de correspondant de l'Utilisateur dans le Serveur de destination de la base de données a été restaurée.

Si:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

Fais pas, essayez ceci:

EXEC sp_change_users_login 'Auto_Fix', 'Username', NULL, 'p@ssword123'

Je l'ai trouvé ici:
http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users

J'ai trouvé le script suivant à partir de Microsoft KB918992 - exécuter sur le serveur d'origine et il va créer une procédure stockée appelée "sp_help_revlogin" qui génère un autre script à exécuter sur le serveur de destination, la création de tous les comptes d'utilisateur avec les mêmes mots de passe et les peid. Fait des merveilles pour notre mise à niveau à partir de SQL2000 à 2008.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF' 
WHILE (@i <= @length) 
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin 
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR 
SELECT sid, name, xstatus, password FROM master..sysxlogins 
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR 
SELECT sid, name, xstatus, password FROM master..sysxlogins 
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr 
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr 
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr 
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN 
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
ELSE 
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr 
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO