Prevenir usuarios huérfanos

En el anterior post aprendimos lo que eran y cómo corregir el problema de usuarios huérfanos, sin embargo, como es mejor prevenir que curar, vamos a aprender cómo prevenir el problema.

A modo de resumen, un usuario huérfano es el que no está relacionado con un login. Esto es muy común cuando movemos bases de datos entre diferentes servidores o cuando tenemos un sistema de alta disponibilidad que replica las bases de datos como el Always On o el Log Shipping. Aunque tengamos logins con el mismo nombre en ambos servidores no tienen el mismo SID. Como ya habréis podido adivinar para no tener más este problema deberemos asignar al login el mismo SID, pero esto solo se puede hacer recreando el login.

Averiguar el SID de un usuario por script

Lo primero que haremos será crear el login normalmente en uno de los servidores. Si ya tenemos los logins creados en ambos servidores lo borraremos de uno de ellos. Con el login creado en solo uno de los servidores usaremos este script para ver el SID:

select name, sid from sys.sql_logins

Copiaremos el sid de nuestro login y cuando vayamos a crearlo en el otro servidor le indicaremos que use ese SID de la siguiente manera

CREATE LOGIN batman WITH PASSWORD = 'G0th@m', SID = 0x8FB57BD6DA61BA40ABAB2A41DDC91A41

Scriptar la contraseña

En el ejemplo anterior hemos creado un login con un SID concreto y una contraseña. Hemos podido hacer esto porque conocíamos la contraseña del usuario pero, en la vida real, esto no siempre es tan bonito. Con SQL no vamos a poder ver las contraseñas de los usuarios en texto plano pero, esto tampoco significa que no podamos hacer nada. Usando la misma vista de antes sys.sql_logins podemos obtener la contraseña cifrada.

select name, sid, password_hash from sys.sql_logins

Con el HASH de la contraseña y el SID recrearemos el login de la siguiente manera:

CREATE LOGIN batman WITH PASSWORD = 0x0200B240435023921FCBA909DCF2136C27F81D0A4BEADBFBB5286E0511C8CF490BFAB9002EE881A8E7423195571D933AB189A9CB3EB5109C78A0789436811F6C833D8233B8B6 HASHED , SID = 0x8FB57BD6DA61BA40ABAB2A41DDC91A41

Copia masiva de logins

Para recrear varios inicios de sesión de golpe podemos hacer uso del procedimiento almacenado sp_help_revlogin. Para poder usarlo primero copiaremos el código del procedimiento desde la web oficial de Microsoft y lo ejecutaremos en nuestra instalación. Con ejecutar el procedimiento tendremos el script para recrear todos los logins con el mismo SID y contraseña.

Conclusión

Hemos aprendido cómo saber el SID y la contraseña cifrada de nuestros logins para transferirlos a otros servidores sin miedo a generar usuarios huérfanos. Esto es muy útil cuando mueves bases de datos entre servidores de manera periódica, como cuando refrescas un servidor de preproducción pero es imprescindible en instalaciones de alta disponibilidad donde en caso de balanceo tiene que ser transparente para los usuarios.