SQL Server: Como exportar los usuarios de una instancia de BBDD

Hola a todos,

Es muy habitual tener que pasar una BBDD de un servidor a otro, y nos encontramos siempre con el problema de los usuarios que se encuentran dados de alta en la instancia de SQL Server.

Los usuarios de dominio no suponen un problema ya que darlos de alta de nuevo no requiere conocer la contraseña de usuario, pero los usuarios dados de alta directamente en SQL Server no podremos volverlos a crear si no conocemos la password de cada usuario.

Para solucionar este problema existe una forma de extraer los usuarios de una instancia con las password (cifradas) y poderlas importar a continuación en la nueva instancia.


Con el procedimiento que vamos a explicar aquí tendremos la ventaja de que exportaremos también el "sid" del usuario, con lo que al importar los usuarios, la BBDD que estamos migrando los reconocerá como suyos. Si no hiciésemos esto, al crear los nuevos usuarios a mano, tendríamos que eliminar los usuarios que tiene la BBDD, ya que los "sid" no coincidirían y los usuarios a nivel de la Instancia y los usuarios a nivel de BBDD se tomarían como usuarios distintos.

SQL SERVER 2005


Pasos a seguir:

1. Ejecutar la consulta (más abajo). Esto creará 2 procedimientos almacenados en la BBDD Master: sp_help_revlogin y sp_hexadecimal.
2. Pulsamos botón derecho sobre sp_help_revlogin y seleccionamos "Execute Stored Procedure". Esto nos dará como resultado una nueva consulta (query).
3. Ejecutamos la nueva consulta generada y y al hacerlo nos devuelve otra consulta con la que podremos generar los usuarios.
4. Copiamos la consulta de generación de usuarios y la ejecutamos en la Instancia del servidor destino.

Con esto ya tendríamos nuestros usuarios creados en la nueva instancia del nuevo servidor.

A continuación os pongo la consulta con la que comenzaremos el proceso. Os comento que también nos va a rescatar el idioma y la bbdd por defecto de cada usuario.

--*************************************************************

--*************************************************************

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 (514) OUTPUT

AS

DECLARE @charvalue varchar (514)

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 @type varchar (1)

DECLARE @hasaccess int

DECLARE @denylogin int

DECLARE @is_disabled int

DECLARE @PWD_varbinary  varbinary (256)

DECLARE @PWD_string  varchar (514)

DECLARE @SID_varbinary varbinary (85)

DECLARE @SID_string varchar (514)

DECLARE @tmpstr  varchar (1024)

DECLARE @is_policy_checked varchar (3)

DECLARE @is_expiration_checked varchar (3)

DECLARE @language varchar (128)

DECLARE @dbname varchar (128)

IF (@login_name IS NULL)

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin, l.language, l.dbname

        FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

        WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

ELSE

  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, l.hasaccess, l.denylogin, l.language, l.dbname

        FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name )

        WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin, @language, @dbname

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 ''

WHILE (@@fetch_status <> -1)

BEGIN

  IF (@@fetch_status <> -2)

  BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF (@type IN ( 'G', 'U'))

    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS'

    END

    ELSE BEGIN -- SQL Server authentication

        -- obtain password and sid

        SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

        EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT

        -- obtain password policy state

        SELECT @is_policy_checked =

            CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

            FROM sys.sql_logins WHERE name = @name

        SELECT @is_expiration_checked =

            CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END

            FROM sys.sql_logins WHERE name = @name

        SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name )

            + ' WITH PASSWORD = ' + @PWD_string

            + ' HASHED, SID = ' + @SID_string

                  + ', DEFAULT_LANGUAGE  = [' + @language

                  + '], DEFAULT_DATABASE  = [' + @dbname + ']'

        IF ( @is_policy_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

        END

        IF ( @is_expiration_checked IS NOT NULL )

        BEGIN

          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

        END

    END

    IF (@denylogin = 1)

    BEGIN -- login is denied access

      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

    END

    ELSE IF (@hasaccess = 0)

    BEGIN -- login has exists but does not have access

      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

    END

    IF (@is_disabled = 1)

    BEGIN -- login is disabled

      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

    END

    PRINT @tmpstr

  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @hasaccess, @denylogin, @language, @dbname

  END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

--*************************************************************

--*************************************************************

SQL SERVER 2000


En esta versión de SQL Server, la cosa cambia.
Primero ejecutaremos una consulta (más abajo), que nos dará como resultado una tabla en la que cada fila de dicha tabla representa una consulta de creación de usuario.
Lo mejor es seleccionar toda la tabla, copiarla (CTRL+C) y pegarla en una nueva consulta en la instancia del servidor destino. La ejecutamos y nos generará los usuarios.

select 'CREATE LOGIN [' + l.name + '] WITH PASSWORD = ', (CAST(l.password as varbinary(256))), ' HASHED, SID = ', CAST(l.sid as varbinary), ', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ' + 'GO ' from master..syslogins l where l.isntname + l.isntgroup = 0