Gestión de usuarios huérfanos en SQL Server: Una guía integral

Gestionar usuarios huérfanos en SQL Server es fundamental para la seguridad y administración de bases de datos. Los usuarios huérfanos ocurren cuando un usuario de base de datos existe sin un inicio de sesión correspondiente en la base de datos maestra. Esta discrepancia puede interrumpir el acceso y llevar a posibles vulnerabilidades de seguridad. En este artículo, exploraremos qué son los usuarios huérfanos, cómo detectarlos y métodos para resolverlos, incluidos ejemplos y escenarios actualizados.

Antecedentes

Para conectarse a una base de datos SQL Server, un usuario debe tener un inicio de sesión válido en la base de datos maestra. Este inicio de sesión autentica al usuario y se mapea a un usuario de base de datos para autorización. El mapeo se mantiene utilizando el Identificador de Seguridad (SID), asegurando que los usuarios de base de datos y los inicios de sesión del servidor estén vinculados correctamente.

Las excepciones a la regla de mapeo de inicio de sesión-usuario incluyen:

1. Usuarios de Base de Datos Contenidos: Se autentican a nivel de base de datos, eliminando la dependencia de los inicios de sesión del servidor. Estos usuarios mejoran la portabilidad de la base de datos, pero requieren recreación separada para cada base de datos.
2. Cuentas de Invitado: Habilitadas por defecto, permiten el acceso a usuarios sin mapeo explícito, pero generalmente se desaconsejan por razones de seguridad.
3. Membresías de Grupos de Microsoft Windows: Los miembros de grupos de Windows pueden acceder a las bases de datos si el grupo es agregado como usuarios.

Un usuario de base de datos se convierte en huérfano cuando:

• El inicio de sesión correspondiente es eliminado.
• La base de datos es migrada o restaurada sin los inicios de sesión asociados.
• El mapeo de SID entre el usuario y el inicio de sesión es incorrecto.

¿Qué son los usuarios huérfanos?

Un usuario huérfano ocurre cuando:

• El usuario de la base de datos existe, pero el inicio de sesión correspondiente en la base de datos maestra no.
• El SID del usuario de la base de datos y el SID del inicio de sesión no coinciden.

Estos escenarios interrumpen la autenticación y la autorización, lo que lleva a errores o acceso restringido.

Detección de usuarios huérfanos

Para SQL Server

Utilice la siguiente consulta para identificar usuarios huérfanos:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

Para Azure SQL Database o Synapse Analytics

1. Recupere los SIDs de los inicios de sesión en la base de datos maestra:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. Recupere los SIDs de los usuarios en la base de datos de destino:

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. Compare las listas para identificar discrepancias.

Resolución de usuarios huérfanos

1. Recrear el inicio de sesión faltante con SID 

Si el inicio de sesión fue eliminado:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. Mapear un usuario huérfano a un inicio de sesión existente 

Si el inicio de sesión existe pero no está mapeado:

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. Cambiar la contraseña del usuario 

Después de recrear el inicio de sesión, actualice su contraseña:

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

Usando sp_FindOrphanedUser

El procedimiento almacenado sp_FindOrphanedUser es una herramienta poderosa para detectar y resolver usuarios huérfanos. Proporciona:

1. Una lista de usuarios huérfanos.
2. Scripts autogenerados para reparaciones.
3. Detalles sobre esquemas pertenecientes a usuarios huérfanos.

Obtén el código del paquete de sp_FindOrphanedUser.

Ejecución del Procedimiento

Ejecución predeterminada:

EXEC sp_FindOrphanedUser;

Para una base de datos específica:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

Ejemplos de Escenarios de Usuario Huérfano

Ejemplo 1: Usuario Sin Inicio de Sesión

Escenario

Se elimina un inicio de sesión, dejando al usuario de la base de datos huérfano.

• Detectar el usuario huérfano:
  MS SQL

  EXEC sp_FindOrphanedUser;

• Recrear el inicio de sesión:
  MS SQL

   

  CREATE LOGIN [User1] WITH PASSWORD = 'SecurePassword123';

  USE [ExampleDB];

  ALTER USER [User1] WITH LOGIN = [User1];

Ejemplo 2: Desajuste de SID

Escenario

El SID de inicio de sesión no coincide con el SID del usuario.

• Detecta la discrepancia:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Corrija el mapeo de SID:
  MS SQL

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

Ejemplo 3: Usuario Huérfano Propietario de un Esquema

Escenario

El usuario huérfano impide la eliminación del esquema.

• Detectar la propiedad del esquema:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• Reasignar la propiedad del esquema:
  MS SQL

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

Mejores Prácticas

• Utilizar Usuarios de Base de Datos Contenidos: Evitar la dependencia de inicios de sesión del servidor.
• Sincronizar Inicios de Sesión: Siempre migrar o restaurar inicios de sesión durante las migraciones de base de datos.
• Auditorías Regulares: Verificar periódicamente si hay usuarios huérfanos para mantener la seguridad.

Conclusión

Gestionar usuarios huérfanos asegura la seguridad de la base de datos y la eficiencia operativa. Al utilizar consultas y herramientas como sp_FindOrphanedUser, puedes identificar y resolver rápidamente problemas, manteniendo un acceso fluido para usuarios legítimos. Adoptar mejores prácticas como usuarios contenidos y auditorías regulares puede prevenir tales problemas en el futuro.