GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Transferir o Mover Contraseñas de Inicios de Sesión de SQL Server entre Servidores


En alguna ocasión, me ha resultado necesario mover o transferir las contraseñas de los Inicios de Sesión de SQL Server entre Instancias. Lo suyo (en muchos casos), es crear los Inicios de Sesión en destino con el mismo SID y Password que en origen, pero si esto no se hizo así, y queremos mantener las mismas contraseñas, deberemos cambiarlas a posteriori, y surge la duda ¿Cómo mover contraseñas de SQL Server entre Instancias? En este artículo, se incluye Scripts de ejemplo para mover contraseñas de SQL Server, considerando las casuísticas de SQL Server 2000 y SQL Server 2005.

El objetivo principal de este artículo, es incluir unos Scripts de ejemplo para mover contraseñas entre Instancias de SQL Server, algo no muy habitual, pero que en alguna ocasión nos puede resultar útil, así que, teniendo ya los Scripts preparados, eso que nos ahorramos. Mover las contraseñas entre Instancias de SQL Server, junto corregir los Usuarios Huérfanos, son tareas habituales en Migraciones y Consolidaciones de Instancias y Bases de Datos SQL Server.

Antes de continuar, es importante recordar que SQL Server almacena el HASH de las contraseñas de los Inicios de Sesión de SQL Server, que podremos consultar en las tablas y/o vistas del sistema. Es decir, las contraseñas no se almacenan en texto claro. Así que, no podemos utilizar el procedimiento almacenado sp_password, puesto que no podremos averiguar la contraseña. Realmente, lo que queremos es obtener el HASH de la contraseña en el origen, y asignar ese HASH al Inicio de Sesión correspondiente en el destino.

Es importante considerar que trataremos los siguientes casos:

  • Mover Contraseñas de SQL Server 2000 a SQL Server 2000.
  • Mover Contraseñas de SQL Server 2000 a SQL Server 2005.
  • Mover Contraseñas de SQL Server 2005 a SQL Server 2005.

Digo esto, porque existen algunas diferencias al respecto entre estas dos versiones de SQL Server, en particular:

  • En SQL Server 2000 la password no es susceptible de mayúsculas y minúsculas, mientras que en SQL Server 2005 SI es susceptible de mayúsculas y minúsculas.
  • En SQL Server 2000 podemos obtener el hash de la contraseña del campo password de la tabla de sistema master.dbo.sysxlogins, mientras que en SQL Server 2005 y SQL Server 2008 necesitaremos recurrir a la vista del sistema master.sys.sql_logins. Existen más detalles, pues en sysxlogins está la definición de todos los Inicios de Sesión (sean Grupos de Windows, Usuarios de Windows, etc.) por lo que deberemos filtrar, mientras que en sql_logins sólo se almacena información de Inicios de Sesión de SQL Server.
  • Para cambiar el HASH de la contraseña de un Inicio de Sesión en SQL Server 2000, deberemos utilizar una sentencia UPDATE sobre la tabla del sistema master.dbo.sysxlogins, para lo cual, previamente deberemos habilitar las actualizaciones de las tablas del sistema (sp_configure ‘allow updates’, 1). Sin embargo, en SQL Server 2005 y SQL Server 2008, podremos utilizar la sentencia ALTER LOGIN WITH PASSWORD HASHED.

Dicho todo esto, estamos en condiciones de presentar el código correspondiente para realizar el cambio de contraseñas. Téngase en cuenta, que la ejecución del código aquí incluido sobre la instancia origen, obtendrá como salida las sentencias que seguidamente deberemos ejecutar sobre la instancia destino. A continuación, se incluyen los scripts correspondientes:

Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2000

DECLARE curDBs CURSOR
READ_ONLY
FOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA)
	SELECt name, password 
	FROM master.dbo.sysxlogins
	WHERE srvid IS NULL
	AND password IS NOT NULL
	AND name IN (SELECT name FROM master.dbo.syslogins 
	             WHERE isntname=0 AND isntgroup=0)
	AND name <> 'sa'
	ORDER BY 1
 
DECLARE @name VARCHAR(256)
DECLARE @password VARBINARY(256)
DECLARE @text_password VARCHAR(256)
OPEN curDBs
 
FETCH NEXT FROM curDBs INTO @name,@password
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        exec sp_hexadecimal @password, @text_password OUT
		DECLARE @message varchar(2000)
		SELECT @message = 'update master.dbo.sysxlogins '
		SELECT @message = @message + 'set password=' + @text_password + ' '
		SELECT @message = @message + 'where name=''' + @name + ''' '
		SELECT @message = @message + 'and srvid is null '
		PRINT @message
	END
	FETCH NEXT FROM curDBs INTO @name,@password
END
 
CLOSE curDBs
DEALLOCATE curDBs
GO

Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2005

DECLARE curDBs CURSOR
READ_ONLY
FOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA)
	SELECt name, password 
	FROM master.dbo.sysxlogins
	WHERE srvid IS NULL
	AND password IS NOT NULL
	AND name IN (SELECT name FROM master.dbo.syslogins 
	             WHERE isntname=0 AND isntgroup=0)
	AND name <> 'sa'
	ORDER BY 1
 
DECLARE @name VARCHAR(256)
DECLARE @password VARBINARY(256)
DECLARE @text_password VARCHAR(256)
OPEN curDBs
 
FETCH NEXT FROM curDBs INTO @name,@password
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        exec sp_hexadecimal @password, @text_password OUT
		DECLARE @message varchar(2000)
		SELECT @message = 'ALTER LOGIN ' + @name + ' '
		SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED'
		PRINT @message
	END
	FETCH NEXT FROM curDBs INTO @name,@password
END
 
CLOSE curDBs
DEALLOCATE curDBs
GO

Mover Contraseñas de SQL Server de SQL Server 2005 a SQL Server 2005

DECLARE curDBs CURSOR
READ_ONLY
FOR -- Inicios de Sesión de SQL Server (excepto SA)
	SELECt name, password_hash AS password 
	FROM master.sys.sql_logins
	WHERE name <> 'sa'
	ORDER BY 1
 
DECLARE @name VARCHAR(256)
DECLARE @password VARBINARY(256)
DECLARE @text_password VARCHAR(256)
OPEN curDBs
 
FETCH NEXT FROM curDBs INTO @name,@password
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        exec sp_hexadecimal @password, @text_password OUT
		DECLARE @message varchar(2000)
		SELECT @message = 'ALTER LOGIN ' + @name + ' '
		SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED'
		PRINT @message
	END
	FETCH NEXT FROM curDBs INTO @name,@password
END
 
CLOSE curDBs
DEALLOCATE curDBs
GO

Por ultimo, aprovecho para colgar los anteriores Scripts, para que resulte más fácil su descarga y utilización, por parte de quienes deseen probarlos (ojo, que son susceptibles de fallo).

Descargar Script Mover Contraseñas de SQL Server 2000 a SQL Server 2000 (cambiar_password_logins_SQLServer2000.sql)

Descargar Script Mover Contraseñas de SQL Server 2000 a SQL Server 2005 (cambiar_password_logins_SQLServer2000_SQLServer2005.sql)

Descargar Script Mover Contraseñas de SQL Server 2005 a SQL Server 2005 (cambiar_password_logins_SQLServer2005.sql)

Como siempre, espero que os guste !

El objetivo principal de este artículo, es incluir unos Scripts de ejemplo para mover contraseñas entre Instancias de SQL Server, algo no muy habitual, pero que en alguna ocasión nos puede resultar útil, así que, teniendo ya los Scripts preparados, eso que nos ahorramos. Mover las contraseñas entre Instancias de SQL Server, junto corregir los Usuarios Huérfanos, son tareas habituales en Migraciones y Consolidaciones de Instancias y Bases de Datos SQL Server.

Antes de continuar, es importante recordar que SQL Server almacena el HASH de las contraseñas de los Inicios de Sesión de SQL Server, que podremos consultar en las tablas y/o vistas del sistema. Es decir, las contraseñas no se almacenan en texto claro. Así que, no podemos utilizar el procedimiento almacenado sp_password, puesto que no podremos averiguar la contraseña. Realmente, lo que queremos es obtener el HASH de la contraseña en el origen, y asignar ese HASH al Inicio de Sesión correspondiente en el destino.

Es importante considerar que trataremos los siguientes casos:

  • Mover Contraseñas de SQL Server 2000 a SQL Server 2000.
  • Mover Contraseñas de SQL Server 2000 a SQL Server 2005.
  • Mover Contraseñas de SQL Server 2005 a SQL Server 2005.

Digo esto, porque existen algunas diferencias al respecto entre estas dos versiones de SQL Server, en particular:

  • En SQL Server 2000 la password no es susceptible de mayúsculas y minúsculas, mientras que en SQL Server 2005 SI es susceptible de mayúsculas y minúsculas.
  • En SQL Server 2000 podemos obtener el hash de la contraseña del campo password de la tabla de sistema master.dbo.sysxlogins, mientras que en SQL Server 2005 y SQL Server 2008 necesitaremos recurrir a la vista del sistema master.sys.sql_logins. Existen más detalles, pues en sysxlogins está la definición de todos los Inicios de Sesión (sean Grupos de Windows, Usuarios de Windows, etc.) por lo que deberemos filtrar, mientras que en sql_logins sólo se almacena información de Inicios de Sesión de SQL Server.
  • Para cambiar el HASH de la contraseña de un Inicio de Sesión en SQL Server 2000, deberemos utilizar una sentencia UPDATE sobre la tabla del sistema master.dbo.sysxlogins, para lo cual, previamente deberemos habilitar las actualizaciones de las tablas del sistema (sp_configure ‘allow updates’, 1). Sin embargo, en SQL Server 2005 y SQL Server 2008, podremos utilizar la sentencia ALTER LOGIN WITH PASSWORD HASHED.

Dicho todo esto, estamos en condiciones de presentar el código correspondiente para realizar el cambio de contraseñas. Téngase en cuenta, que la ejecución del código aquí incluido sobre la instancia origen, obtendrá como salida las sentencias que seguidamente deberemos ejecutar sobre la instancia destino. A continuación, se incluyen los scripts correspondientes:

Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2000

DECLARE curDBs CURSOR
READ_ONLY
FOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA)
	SELECt name, password 
	FROM master.dbo.sysxlogins
	WHERE srvid IS NULL
	AND password IS NOT NULL
	AND name IN (SELECT name FROM master.dbo.syslogins 
	             WHERE isntname=0 AND isntgroup=0)
	AND name <> 'sa'
	ORDER BY 1
 
DECLARE @name VARCHAR(256)
DECLARE @password VARBINARY(256)
DECLARE @text_password VARCHAR(256)
OPEN curDBs
 
FETCH NEXT FROM curDBs INTO @name,@password
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        exec sp_hexadecimal @password, @text_password OUT
		DECLARE @message varchar(2000)
		SELECT @message = 'update master.dbo.sysxlogins '
		SELECT @message = @message + 'set password=' + @text_password + ' '
		SELECT @message = @message + 'where name=''' + @name + ''' '
		SELECT @message = @message + 'and srvid is null '
		PRINT @message
	END
	FETCH NEXT FROM curDBs INTO @name,@password
END
 
CLOSE curDBs
DEALLOCATE curDBs
GO

Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2005

DECLARE curDBs CURSOR
READ_ONLY
FOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA)
	SELECt name, password 
	FROM master.dbo.sysxlogins
	WHERE srvid IS NULL
	AND password IS NOT NULL
	AND name IN (SELECT name FROM master.dbo.syslogins 
	             WHERE isntname=0 AND isntgroup=0)
	AND name <> 'sa'
	ORDER BY 1
 
DECLARE @name VARCHAR(256)
DECLARE @password VARBINARY(256)
DECLARE @text_password VARCHAR(256)
OPEN curDBs
 
FETCH NEXT FROM curDBs INTO @name,@password
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        exec sp_hexadecimal @password, @text_password OUT
		DECLARE @message varchar(2000)
		SELECT @message = 'ALTER LOGIN ' + @name + ' '
		SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED'
		PRINT @message
	END
	FETCH NEXT FROM curDBs INTO @name,@password
END
 
CLOSE curDBs
DEALLOCATE curDBs
GO

Mover Contraseñas de SQL Server de SQL Server 2005 a SQL Server 2005

DECLARE curDBs CURSOR
READ_ONLY
FOR -- Inicios de Sesión de SQL Server (excepto SA)
	SELECt name, password_hash AS password 
	FROM master.sys.sql_logins
	WHERE name <> 'sa'
	ORDER BY 1
 
DECLARE @name VARCHAR(256)
DECLARE @password VARBINARY(256)
DECLARE @text_password VARCHAR(256)
OPEN curDBs
 
FETCH NEXT FROM curDBs INTO @name,@password
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        exec sp_hexadecimal @password, @text_password OUT
		DECLARE @message varchar(2000)
		SELECT @message = 'ALTER LOGIN ' + @name + ' '
		SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED'
		PRINT @message
	END
	FETCH NEXT FROM curDBs INTO @name,@password
END
 
CLOSE curDBs
DEALLOCATE curDBs
GO

Por ultimo, aprovecho para colgar los anteriores Scripts, para que resulte más fácil su descarga y utilización, por parte de quienes deseen probarlos (ojo, que son susceptibles de fallo).

Descargar Script Mover Contraseñas de SQL Server 2000 a SQL Server 2000 (cambiar_password_logins_SQLServer2000.sql)

Descargar Script Mover Contraseñas de SQL Server 2000 a SQL Server 2005 (cambiar_password_logins_SQLServer2000_SQLServer2005.sql)

Descargar Script Mover Contraseñas de SQL Server 2005 a SQL Server 2005 (cambiar_password_logins_SQLServer2005.sql)

Como siempre, espero que os guste !


[Fecha del Artículo (UTC): 05/06/2009]
[Autor: GuilleSQL]



Escribir un Comentario

Para poder escribir un comentario, debe Iniciar Sesión con un usuario.

Si no dispone de un usuario, puede Registrarse y hacerse miembro.

Si dispone de un usuario, pero no recuerda sus credenciales de acceso, puede Restablecer su Contraseña.

Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Julio de 2018 (1)
Junio de 2018 (4)
Mayo de 2018 (5)
Abril de 2018 (3)
Marzo de 2018 (2)
Febrero de 2018 (7)
Enero de 2018 (1)
Diciembre de 2017 (15)
Noviembre de 2017 (7)
Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Esta información se proporciona "como está" sin garantías de ninguna clase, y no otorga ningún derecho.
This information is provided "AS IS" with no warranties, and confers no rights.

Copyright © 2007 GuilleSQL, todos los derechos reservados.
GuilleSQL.com y GuilleSQL.net son también parte de Portal GuilleSQL.

Visitas recibidas (Page Loads) en GuilleSQL (fuente: StatCounter):

screen resolution stats
Visitas