Como pasar Logins de SQL Server entre servidores

En este post voy a explicar uno de los dolores de cabeza mas frecuentes que tenemos y es el pasar los logins entre servidores.

Sabemos que en SQLServer hay 2 tipos de login, los de Windows y los de SQL, los primeros son usuarios del dominio que tambien tienen acceso al servidor de base de datos y los segundos son usuarios que se guardan dentro del SQLServer, o sea, el password esta dentro del SQLServer , en lugar de la de windows que la controla el controlador de dominio.

Lo recomendado es tratar de tener y usar siempre usuarios Windows, la recomendacion se debe a que le dejaremos toda la administracion (complejidad de password, control de vencimientos, etc) al sistema operativo y ademas permitirle a un usuario que no deba volver a hacer login (si usara usuarios SQL) cuando ya lo hizo al entrar a su windows.

Bien, como hacemos si deseamos pasar los logins SQL y los de windows de un servidor a otro, esto podria ser a la misma version de SQL o bien a distintas versiones, por ej se desea migrar todo el servidor 7 y 2000 a un 2005 pero no es cuestion de solo migrar las bases sino tambien los logins entre otras cosas.

Si deseamos migrar los usuarios SQL podriamos por ejemplo desde SQL2005 usar Integration Service (el reemplazo de DTS) y usar una tarea para esa funcion, la desventaja de hacer esto es que pasara los logins pero no las password con lo cual le deberiamos decir a todos los usuarios SQL que vuelvan a cambiar las password (nada agradable no?)

Lo que les voy a dejar es un script el cual nos permitira migrar los usuarios de un server a otro, este script no solo migra usuarios SQL sino tambien los de windows y lo mas lindo de esto es que se puede usar para migrar de 7 a 2005 , de 7 a 2000 o de 2000 a 2005.

Estos seran los pasos que debemos realizar para migrar usuarios:

• Generamos SP del script en el servidor origen.
• Ejecutamos el SP en el servidor origen.
• El resultado del SP lo copiamos a un archivo .sql
• Ejecutamos este archivo .sql en el servidor destino.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date:
03/23/2006 10:24:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create PROCEDURE [dbo].[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

Ahora debemos ejecutar como hemos mencionado anteriormente el SP_HELP_REVLOGIN y su resultado guardarlo como .sql para luego ejecutarlo en el servidor destino.

use master
go
EXEC sp_help_revlogin

Como podemos obervar el SP ha recorrido todos los usuarios de mi servidor (menos el SA) y ha dado como resultado las sentencias de comando que debo ejecutar en mi servidor destino.

Las claves seran pasadas pero como se observa no podemos identificar cual es la misma, con lo cual es un proceso totalmente seguro ya que lo unico que aseguramos que pasen los logins y sus claves tambien para que el usuario se pueda conectar sin ningun problema en el servidor destino.

Bueno espero que el truco les sea de utilidad.