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