Solución de problemas de usuarios huérfanos (SQL Server)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Los usuarios huérfanos en SQL Server se producen cuando un usuario de base de datos se basa en un inicio de sesión en la base de datos maestra, pero ese inicio de sesión ya no existe en master. Esto puede suceder cuando se elimina el inicio de sesión o cuando la base de datos se mueve a otro servidor donde el inicio de sesión no existe. En este tema se describe cómo buscar usuarios huérfanos para reasignarles inicios de sesión.

Información previa

Para conectarse a una base de datos en una instancia de SQL Server con una entidad de seguridad (identidad de usuario de base de datos) basada en un inicio de sesión, la entidad de seguridad debe tener un inicio de sesión válido en la base de datos maestra . Este inicio de sesión se usa en el proceso de autenticación, que comprueba la identidad de la entidad de seguridad y averigua si tiene permiso para conectarse a la instancia de SQL Server. Los inicios de sesión de SQL Server en una instancia de servidor están visibles en la vista de catálogo sys.server_principals y en la vista de compatibilidad sys.sql_logins .

SQL Server tienen acceso a las bases de datos individuales como un "usuario de base de datos" que está asignado al inicio de sesión de SQL Server. Existen tres excepciones a esta regla:

• Usuarios de bases de datos independientes

  Los usuarios de bases de datos independientes se autentican en el nivel de base de datos de usuario y no están asociados a inicios de sesión. Esto es recomendable porque las bases de datos son más portátiles y los usuarios de las bases de datos independientes no pueden quedarse huérfanos. Sin embargo, deben volver a crearse para cada base de datos, algo muy poco práctico en un entorno con muchas bases de datos.

• La cuenta Invitado .

  Si esta cuenta se habilita en la base de datos, permite que los inicios de sesión de SQL Server que no están asignados a un usuario de base de datos puedan tener acceso a la base de datos como un usuario invitado . La cuenta Invitado está deshabilitada de forma predeterminada.

• La pertenencia a grupos de Microsoft Windows.

  Un inicio de sesión de SQL Server creado desde un usuario de Windows puede entrar en una base de datos si este usuario es miembro de un grupo de Windows que también sea usuario en la base de datos.

La información sobre la asignación de un inicio de sesión de SQL Server a un usuario de la base de datos se almacena en la base de datos. Incluye el nombre del usuario de la base de datos y el SID del inicio de sesión de SQL Server correspondiente. Los permisos de este usuario de base de datos se usan para otorgar autorizaciones en la base de datos.

Un usuario de base de datos (basado en un inicio de sesión) cuyo inicio de sesión de SQL Server correspondiente está sin definir o se ha definido de forma incorrecta en una instancia de servidor no podrá iniciar una sesión en la instancia. Es lo que se denomina un usuario huérfano de la base de datos en esa instancia de servidor. Otra manera de convertirse en huérfano es que el SID de inicio de sesión al que está asignado el usuario de la base de datos no esté presente en la instancia master . Un usuario de la base de datos puede convertirse en huérfano si una base de datos se restaura o se conecta a otra instancia de SQL Server donde nunca se ha creado el inicio de sesión. También puede convertirse en huérfano si se quita el inicio de sesión de SQL Server correspondiente. Incluso si se vuelve a crear el inicio de sesión, tendrá un SID diferente, por lo que el usuario de la base de datos seguirá siendo huérfano.

Detección de usuarios huérfanos

Para SQL Server y PDW

Para detectar usuarios huérfanos en SQL Server por no tener inicios de sesión de autenticación de SQL Server , ejecute la siguiente instrucción en la base de datos de usuario:

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';  

En la salida se enumeran los usuarios de autenticación de SQL Server y los identificadores de seguridad (SID) correspondientes en la base de datos actual que no están vinculados a ningún inicio de sesión de SQL Server.

Para SQL Database y Azure Synapse Analytics

La tabla sys.server_principals no está disponible en SQL Database ni en Azure Synapse Analytics. Identifique los usuarios huérfanos de esos entornos con los siguientes pasos:

1. Conéctese a la base de datos master y seleccione los SID para los inicios de sesión con la siguiente consulta:

   SELECT sid 
   FROM sys.sql_logins 
   WHERE type = 'S'; 
   

2. Conéctese a la base de datos de usuario y revise los SID de los usuarios de la tabla sys.database_principals mediante la consulta siguiente:

   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 dos listas para determinar si hay SID de usuario en la tabla sys.database_principals de la base de datos de usuario que no coinciden con los SID de inicio de sesión de la tabla sql_logins de la base de datos maestra.

Resolución de un usuario huérfano

En la base de datos maestra, use la instrucción CREATE LOGIN con la opción SID para volver a crear un inicio de sesión que falte. Para ello, proporcione el SID del usuario de base de datos obtenido en la sección anterior:

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

Para asignar un usuario huérfano a un inicio de sesión que ya existe en master, ejecute la instrucción ALTER USER en la base de datos de usuario especificando el nombre de inicio de sesión.

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

Cuando se vuelve a crear un inicio de sesión que falta, el usuario puede tener acceso a la base de datos con la contraseña proporcionada. Tras esto, el usuario puede modificar la contraseña de la cuenta de inicio de sesión con la instrucción ALTER LOGIN.

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

Consulte también

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_loginssys.syslogins (Transact-SQL)