Compartir a través de

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 están huérfanos en SQL Server cuando un usuario de base de datos se basa en un inicio de sesión en la master base de datos, pero el inicio de sesión ya no existe en master. Esto puede ocurrir cuando se elimina el inicio de sesión o cuando la base de datos se mueve a otro servidor en el que el inicio de sesión no existe. En este artículo se describe cómo buscar usuarios huérfanos y reasignarlos a inicios de sesión.

Nota

Para reducir la posibilidad de que se produzcan usuarios huérfanos, use usuarios de bases de datos independientes para aquellas bases de datos susceptibles de moverse. Para obtener más información, vea Uso de bases de datos independientes - Conversión de la base de datos en portátil.

Información previa

Para las conexiones a una base de datos en una instancia de SQL Server que usan 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 master base de datos. Este inicio de sesión se usa en el proceso de autenticación que comprueba la identidad de la entidad de seguridad y determina si la entidad de seguridad puede 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 .

Los inicios de sesión de SQL Server acceden a bases de datos individuales como un "usuario de base de datos" 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. Este modelo se recomienda porque las bases de datos son más portátiles y los usuarios de bases de datos independientes no pueden quedar huérfanos. Sin embargo, deben volver a crearse para cada base de datos. Este modelo podría ser poco práctico en un entorno que tenga muchas bases de datos.

  • La cuenta de invitado

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

  • Pertenencias a grupos de Microsoft Windows

    Un inicio de sesión de SQL Server creado a partir de un usuario de Windows puede acceder a una base de datos si el usuario de Windows es miembro de un grupo de Windows que también es un usuario de la base de datos.

La información sobre la asignación de un inicio de sesión de SQL Server a un usuario de base de datos se almacena en la base de datos. Incluye el nombre del usuario de la base de datos y el identificador de seguridad (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) para el que el inicio de sesión de SQL Server correspondiente no está definido o está definido incorrectamente en una instancia del servidor no puede iniciar sesión en la instancia. Es lo que se denomina un usuario huérfano de la base de datos en esa instancia de servidor. La huérfana puede ocurrir si el usuario de la base de datos está asignado a un inicio de sesión SID que no está presente en la master base de datos. 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. Aunque se vuelva a crear el inicio de sesión, tendrá un valor diferente SID, 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 en función de los inicios de sesión de autenticación de SQL Server que faltan, 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 SID correspondientes de la base de datos actual que no están vinculados a ningún inicio de sesión de SQL Server.

Para Azure SQL Database y Azure Synapse Analytics

La tabla sys.server_principals no está disponible en SQL Database o Azure Synapse Analytics. Identifique a los usuarios huérfanos en esos entornos siguiendo estos pasos:

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

    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 sys.database_principals tabla 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 de base de datos sys.database_principals de usuario que no coinciden con los SID de inicio de sesión de la master tabla de base de datos sql_logins .

Resolución de un usuario huérfano

En la master base de datos, use la instrucción CREATE LOGIN con la SID opción para volver a crear un inicio de sesión que falta. Proporcione el SID del usuario de la base de datos que obtuvo 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 y especifique el nombre de inicio de sesión:

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

Al volver a crear un inicio de sesión que falta, el usuario puede acceder a la base de datos mediante la contraseña proporcionada. A continuación, el usuario puede cambiar la contraseña de la cuenta de inicio de sesión mediante la ALTER LOGIN instrucción :

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

Importante

Cualquier inicio de sesión puede cambiar su propia contraseña. Solo los inicios de sesión con el permiso ALTER ANY LOGIN pueden cambiar la contraseña de inicio de sesión de otro usuario. Sin embargo, solo los miembros del rol sysadmin pueden modificar las contraseñas de los miembros del rol sysadmin .

Contenido relacionado

  • Last updated on