Como pasar Logins entre servidores

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


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

Script


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


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


 

15 thoughts on “Como pasar Logins entre servidores”

  1. Parece estar muy bueno! Lo voy a probar

    Te agradeceria mucho si detallas como se genera el archivo y como se copia a un archivo sql
    No tengo mucho en esto y si estoy algo confundido

    saludos cordiales

    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

  2. ESTO ME FUNCIONO PARA MIRAR LAS CUENTAR DE SQL 2000 A 2000. PERO NO ME FUNCIONO DE SQL 2000 A 2005.

    A LA HORA DE EJECUTAR:

    use master
    go
    EXEC sp_help_revlogin

    ME SALE EL SIGUIENTE ERROR:

    Msg 15021, Level 16, State 1, Line 1
    Invalid value given for parameter PASSWORD.

  3. Ejecutandolo en un SQL Server 2005 – 9.00.2153.00 me tira lo siguiente:

    Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 30
    Invalid object name ‘master..sysxlogins’.

    Parece que en 2005 no funciona.

  4. Hola, un gusto saludar

    El script esta muy bueno pero no me funciona en SQL 2005 alguna idea de como hacerlo funcionar?

  5. Para que funcione en SQL Server 2008 debe cambiarse la linea
    sid, name, xstatus, password FROM master..sysxlogins
    por
    SELECT sid, name, status, password FROM master.sys.syslogins

    de igual forma debe cambiarse la linea

    sid, name, xstatus, password FROM master..sysxlogins

    por
    sid, name, status, password FROM master.sys.syslogins

    Con esta modificacion, funciona bien para 2005 y 2008, asi que ya tenemos la forma de hacerlo.

    Saludos

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>