sql-server - seguridad - restaurar base de datos sql server query

Vinculación de usuarios para iniciar sesión después de restaurar una base de datos de SQL Server 2005

Question

Deseo mover una base de datos entre dos servidores, he respaldado la base de datos desde el primer servidor y he hecho una restauración de la base de datos en el segundo servidor, hasta ahora todo bien.

Sin embargo, nuestra aplicación hace uso de muchos usuarios de bases de datos que están definidos en la base de datos. Estos tienen que estar vinculados a los inicios de sesión que se definen en la base de datos maestra. El servidor al que he restaurado la base de datos tiene todos los inicios de sesión definidos, sin embargo, tienen diferentes sids.

No soy un experto en T-SQL ...

Creo que sp_change_users_login es parte de la solución, pero no puedo encontrar la manera de conseguir que vincule automáticamente a todos los usuarios de la base de datos restaurada con el nombre de usuario.

Los scripts de creación de base de datos que utilizamos para nuestra aplicación crean los usuarios y los inicios de sesión, sin embargo, no especifica el SID al crear el inicio de sesión, de ahí este problema. Ahora si tuviera una máquina del tiempo ...

(Cuando en Google obtengo muchos éxitos, sin embargo, en su mayoría son sitios que no le permiten ver la respuesta sin tener que registrarse primero en el sitio).

Answer 1

Encontré el siguiente script de Microsoft KB918992 : ejecútelo en el servidor original y creará un procedimiento almacenado llamado ''sp_help_revlogin'' que genera otro script para ejecutar en el servidor de destino, creando todas las cuentas de usuario con las mismas contraseñas y sids. Funcionó de maravilla para nuestra actualización de SQL2000 a 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

Answer 2

Este fenómeno se llama "usuarios huérfanos".

Aquí hay un artículo sobre él y cómo solucionarlo:

http://sqlblog.com/blogs/eric_johnson/archive/2008/10/17/fixing-orphaned-users.aspx

Y aquí está el artículo de Books Online sobre el comando:

http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx

Answer 3

Sí, puedes hacer eso ejecutando:

EXEC sp_change_users_login ''Auto_Fix'' , ''TheUserName'';

Sin embargo, si su pregunta es si puedo arreglar todos los usuarios automáticamente, esto no hará eso.

Answer 4

Se me ocurrió lo siguiente. Funciona muy bien porque te muestra:

1. Todos los usuarios huérfanos actuales.
2. Cuales fueron arreglados.
3. Cuáles no pudieron ser arreglados.

Otras soluciones requieren que conozcas el nombre de usuario huérfano de antemano para solucionarlo.

El siguiente código podría ejecutarse en un sproc al que se llama después de restaurar una base de datos a otro servidor.

Guión:

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.

Resultado:

* Nota: Los 4 que no fueron corregidos (en mi captura de pantalla de ejemplo anterior) no tenían un Usuario correspondiente en el Servidor de destino al que se restauró la base de datos.

Answer 5

Si:

EXEC sp_change_users_login ''Auto_Fix'' , ''TheUserName'';

No funciona, prueba esto:

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

Lo encontré aquí: http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users

Answer 6

Tengo un buen script que puede usar para crear inicios de sesión de los usuarios de la base de datos, que encontré después de buscar este problema, este script usa un procedimiento almacenado. puede encontrar algunos otros scripts útiles aquí también en esta url http://www.sqlserveroptimizer.com/2011/08/how-to-script-logins-from-user-database-in-sql-server-20052008-r2/

USE MyDatabaseName
DECLARE @login nvarchar(50)
DECLARE logins_cursor CURSOR FOR SELECT l.name FROM sys.database_principals u INNER JOIN sys.server_principals l ON u.sid=l.sid
OPEN logins_cursor FETCH NEXT FROM logins_cursor INTO @login
WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_help_revlogin @login FETCH NEXT FROM logins_cursor INTO @login END
CLOSE logins_cursor DEALLOCATE logins_cursor GO