Como pasar Logins entre servidores

Maxi Accotto — Thu, 08 Feb 2007 00:50:00 GMT

Hola, he vuelto de mis vacaciones y me estoy poniendo al dia, prometo tratar de escribir mucho mas seguido por aqui.

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, la 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.

OK, hay varios servidores donde me ha tocado trabajar donde no solo hay usuarios windows sino que tambien muchos SQL, esto es por lo general por el tipo de aplicaciones que usan SQLServer y como han sido pensadas, pero eso ya es otro tema que discutiremos en otro momento

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

Script

USE [master]

/****** Object: StoredProcedure [dbo].[sp_hexadecimal] Script Date:

03/23/2006 10:24:06 ******/

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[sp_hexadecimal]

@binvalue varbinary(256),

@hexvalue varchar(256) OUTPUT

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

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

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

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

EXEC sp_help_revlogin

El resultado de este SP por ej en mi caso es el siguiente:

/* sp_help_revlogin script --

** Generated Feb 7 2007 10:13PM on NOTEBOOK */ --

DECLARE @pwd sysname --

-- Login: BUILTIN\Administrators --

EXEC master..sp_grantlogin 'BUILTIN\Administrators' --

-- Login: maxi --

SET @pwd = CONVERT (varbinary(256), 0x010063041400C219E42B05857103FA7537624D18A08EF952471C43B39E21C47C5A098B75FAE89F36B27D912666AA) --

EXEC master..sp_addlogin 'maxi', @pwd, @sid = 0xF6630592B345974588C021007A10A99A, @encryptopt = 'skip_encryption' --

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

BLOG DE MAXI ACCOTTO : pasar logins

Como pasar Logins entre servidores