Identificar y resolver usuarios huérfanos en SQL Server con sp_change_users_login

Hola, chicos,
¿Todo muy bien?

En este post les voy a hablar un poco de un problema que he visto mucho en las bases de datos donde he estado brindando consultoría, que son los usuarios huérfanos en la base de datos.

Un poco sobre permisos en SQL Server

En SQL Server existe el concepto de Login, que es el usuario a nivel de instancia de base de datos y también es el objeto que se asocia a la contraseña, caducidad de contraseña y políticas de seguridad, permisos a nivel de servidor, etc.

Además, también existe el concepto de usuario, que se crea para cada base de datos de la instancia y tiene los privilegios de la base de datos en cuestión. Por lo tanto, una persona tiene un inicio de sesión en la instancia y uno o más usuarios creados, porque para cada base de datos, debe tener su propio usuario (no es necesario que sea el mismo nombre) para recibir permisos específicos en cada base de datos (a menos que el inicio de sesión de la persona esté en un rol que le otorgue privilegios en todas las bases de datos, como el rol de administrador). administrador de sistemas).

SQL Server todavía tiene el concepto de grupo AD, donde si usted es parte de un grupo AD y este grupo se crea en SQL Server en forma de inicio de sesión, no será necesario crear un inicio de sesión para cada miembro del grupo AD, solo para el grupo en sí. En este caso, todos los miembros de este grupo tendrán los mismos permisos a nivel de instancia y podrán conectarse al banco utilizando su nombre de usuario y contraseña de AD. (Si el grupo tiene privilegios para hacerlo)

¿Qué son los usuarios huérfanos?

En teoría, todos los usuarios, de todas las bases de datos, deben hacer referencia a un inicio de sesión para que sea posible utilizar ese inicio de sesión para autenticarse en la base de datos. Cuando hay un usuario que no tiene esta referencia, llamamos huérfano a ese usuario.

Una cosa que debe quedar muy clara cuando encontramos un usuario de SQL Server sin inicio de sesión es que existe una diferencia entre un usuario huérfano y un usuario sin inicio de sesión:

Usuario huérfano: este usuario de SQL fue creado y asignado a un inicio de sesión. Por alguna razón (posiblemente una restauración bancaria), este usuario perdió la asociación con el inicio de sesión y permaneció en este estado de usuario sin iniciar sesión. Este tipo de usuario se puede reasignar para iniciar sesión si se pierde esta asociación y se puede utilizar para iniciar sesión en SQL Server.
Usuario sin iniciar sesión: Este usuario de SQL Server se creó utilizando la opción "Usuario SQL sin inicio de sesión" y no estuvo asociado con ningún inicio de sesión durante la creación. En este escenario, No se puede asignar este usuario a un inicio de sesión. (a menos que elimine el usuario, lo vuelva a crear asociándolo con un inicio de sesión y vuelva a aplicar los permisos que tenía) y, como resultado, no es posible iniciar sesión en SQL Server con ese usuario.
Este escenario es muy utilizado por motivos de seguridad, donde se necesita que un usuario sea el propietario de los objetos, pero no se desea poder utilizarlo para iniciar sesión en la base de datos o realizar cambios en datos o estructuras, como podemos leer en el artículo de Pinal Dave. en este enlace aquí.

¿Cómo sucede esto?

SQL Server intenta impedirle crear un usuario sin especificar su inicio de sesión:

CREATE USER [Teste]
GO

Mensaje 15007, Nivel 16, Estado 1, Línea 1
'Teste' no es un inicio de sesión válido o no tienes permiso.

Ahora vamos a crearlo bien:

USE [master]
CREATE LOGIN [Usuario_Teste] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [Testes]
CREATE USER [Usuario_Orfao] FOR LOGIN [Usuario_Teste] WITH DEFAULT_SCHEMA=[dbo]
GO

Listo. Creamos nuestro login y nuestro usuario:

Ahora bien, ¿qué pasa con el usuario si eliminamos su inicio de sesión?

USE [master]
DROP LOGIN [Usuario_Teste]
GO

Es decir, de esta forma, aunque tengamos nuestro usuario creado, no podremos iniciar sesión en el banco, ya que nuestro login ya no existe. Entonces tenemos un usuario huérfano en la base.. Y esto es un problema, ya que es un usuario “inútil” en el banco, ya que sin iniciar sesión no es posible conectarse al banco.

¿Cómo identificar usuarios huérfanos en una base de datos?

USE [Testes]
EXEC sp_change_users_login 'Report'

O usando sys.syslogins (SQL Server 2000 en adelante):

SELECT
    A.name AS UserName,
    A.[sid] AS UserSID
FROM
    sys.sysusers A WITH(NOLOCK)
    LEFT JOIN sys.syslogins B WITH(NOLOCK) ON A.[sid] = B.[sid]
WHERE
    A.issqluser = 1 
    AND SUSER_NAME(A.[sid]) IS NULL 
    AND IS_MEMBER('db_owner') = 1 
    AND A.[sid] != 0x00
    AND A.[sid] IS NOT NULL 
    AND ( LEN(A.[sid]) <= 16 ) 
    AND B.[sid] IS NULL
ORDER BY
    A.name

O usando sys.database_principals (SQL Server 2008 en adelante):

SELECT
    A.name AS UserName,
    A.[sid] AS UserSID
FROM
    sys.database_principals A WITH(NOLOCK)
    LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.name NOT LIKE '##MS_%'
    AND A.[type_desc] = 'SQL_USER'
    AND C.[type_desc] = 'SQL_LOGIN'
    AND A.name NOT IN ('sa')
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name

USE [Testes]
EXEC sp_change_users_login 'Auto_Fix', 'Usuario_Orfao' -- Isso irá associar o Login 'Usuario_Orfao' ao usuário 'Usuario_Orfao'
GO

Si tiene éxito, SQL Server devolverá los siguientes mensajes y, al ejecutar nuevamente el comando EXEC sp_change_users_login 'Report', este usuario ya no aparecerá en las líneas devueltas:

La fila del usuario 'Usuario_Orfao' se solucionará actualizando su enlace de inicio de sesión a un inicio de sesión que ya existe.
El número de usuarios huérfanos solucionados al actualizar los usuarios fue 1.
La cantidad de usuarios huérfanos que se solucionó agregando nuevos inicios de sesión y luego actualizando a los usuarios fue 0.

-- Identificando todos os usuários órfãos da instância
IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos
CREATE TABLE #Usuarios_Orfaos (
    [Ranking] INT IDENTITY(1,1),
    [Database] sysname,
    [Username] sysname,
    [SID] UNIQUEIDENTIFIER,
    [Command] VARCHAR(MAX)
)

INSERT INTO #Usuarios_Orfaos
EXEC master.dbo.sp_MSforeachdb '
SELECT
    ''?'' AS [database],
    A.name,
    A.[sid],
    ''EXEC [?].[sys].[sp_change_users_login] ''''Auto_Fix'''', '''''' + A.name + '''''''' AS command
FROM
    [?].sys.database_principals A WITH(NOLOCK)
    LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.name NOT LIKE ''##MS_%''
    AND A.[type_desc] = ''SQL_USER''
    AND C.[type_desc] = ''SQL_LOGIN''
    AND A.name NOT IN (''sa'')
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name'


-- Exibindo os usuários órfãos da instância
SELECT * FROM #Usuarios_Orfaos


-- Executando os comandos no banco
DECLARE @Comando VARCHAR(MAX) = ''

SELECT @Comando += Command + '; '
FROM #Usuarios_Orfaos

EXEC(@Comando)

USE [Testes]
EXEC sp_change_users_login 
    'Update_One', 
    'Usuario_Orfao',  -- Usuário
    'Usuario_Teste'   -- Login
GO

USE [Testes]
EXEC sp_change_users_login 
    'Auto_Fix', 
    'Usuario_Orfao',  -- Usuário
    NULL,             -- Login. Deixar NULL para criar um novo com o mesmo nome do usuário
    '123'             -- Senha do Login que será criado
GO

Regreso de SQL Server:

Salvo conflicto, la fila del usuario 'Usuario_Orfao' se solucionará actualizando su enlace a un nuevo inicio de sesión.
El número de usuarios huérfanos solucionados al actualizar los usuarios fue 0.
La cantidad de usuarios huérfanos solucionados al agregar nuevos inicios de sesión y luego actualizar a los usuarios fue 1.

Error común que ocurre en SQL Server cuando intentas AUTO_FIX a un usuario huérfano y este usuario ya ha sido creado con la opción SIN INICIO DE SESIÓN. En este caso, Auto_Fix no solucionará el problema y devolverá este mensaje de error:

Mensaje 15600, Nivel 15, Estado 1, Procedimiento sp_change_users_login, Línea 214
Se especificó un parámetro u opción no válido para el procedimiento 'sys.sp_change_users_login'.

En este caso, la solución es solucionar el problema manualmente, utilizando las soluciones presentadas anteriormente.

Una cosa que debe quedar muy clara cuando encontramos un usuario de SQL Server sin inicio de sesión es que existe una diferencia entre un usuario huérfano y un usuario sin inicio de sesión:

Usuario huérfano: este usuario de SQL fue creado y asignado a un inicio de sesión. Por alguna razón (posiblemente una restauración bancaria), este usuario perdió la asociación con el inicio de sesión y permaneció en este estado de usuario sin iniciar sesión. Este tipo de usuario se puede reasignar para iniciar sesión si se pierde esta asociación y se puede utilizar para iniciar sesión en SQL Server.
Usuario sin iniciar sesión: Este usuario de SQL Server se creó utilizando la opción "Usuario SQL sin inicio de sesión" y no estuvo asociado con ningún inicio de sesión durante la creación. En este escenario, No se puede asignar este usuario a un inicio de sesión.y, por lo tanto, no es posible iniciar sesión en SQL Server con este usuario. Este escenario se usa ampliamente por razones de seguridad, donde se necesita que un usuario sea el propietario de los objetos, pero no desea poder usarlo para iniciar sesión en la base de datos o realizar cambios en datos o estructuras. Si intenta asignar este tipo de usuario a un inicio de sesión, encontrará el mensaje de error anterior.

Otra forma de arreglar usuarios huérfanos es usar el comando ALTER USER:

ALTER USER [Usuario] WITH LOGIN = [Login]

Para identificar y corregir usuarios huérfanos de la instancia, en todas las bases de datos, con el comando ALTER USER, utilice el siguiente script:

-- Identificando todos os usuários órfãos da instância
IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos
CREATE TABLE #Usuarios_Orfaos (
    [Ranking] INT IDENTITY(1,1),
    [Database] sysname,
    [Username] sysname,
    [SID] UNIQUEIDENTIFIER,
    [Command] VARCHAR(MAX)
)

INSERT INTO #Usuarios_Orfaos
EXEC master.dbo.sp_MSforeachdb '
SELECT
    ''?'' AS [database],
    A.name,
    A.[sid],
    ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH LOGIN = ['' + A.[name] + '']'' AS command
FROM
    [?].sys.database_principals A WITH(NOLOCK)
    LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.[type_desc] = ''SQL_USER''
    AND C.[type_desc] = ''SQL_LOGIN''
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name'

SELECT * FROM #Usuarios_Orfaos

Resultado:

Nota: prefiero este método como sp_change_users_login está marcado como obsoleto y puede eliminarse en futuras ediciones de SQL Server.

Conclusión

Recuerde que SQL Server tiene el concepto de Grupos de Acceso provenientes de AD. Si hay un inicio de sesión para un grupo de AD en su instancia y necesita otorgar acceso individual a las bases de datos para ciertos usuarios, estos usuarios probablemente no estarán asociados con el inicio de sesión y, por lo tanto, serán usuarios huérfanos.

En este caso, estos usuarios huérfanos no tienen ningún efecto negativo en su base de datos, ya que normalmente acceden a la base de datos a través del grupo AD y tienen permisos individuales en determinadas bases de datos.

Por lo tanto, tenga mucho cuidado al identificar y eliminar/reparar usuarios huérfanos, ya que los comandos vistos anteriormente pueden arrojar falsos positivos para usuarios huérfanos en esta situación.

Eso es todo,
¡Hasta la próxima!

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…

Un poco sobre permisos en SQL Server

¿Qué son los usuarios huérfanos?

¿Cómo sucede esto?

¿Cómo identificar usuarios huérfanos en una base de datos?

Ya he recreado el inicio de sesión. ¿Cómo vuelvo a crear la asociación Iniciar sesión x Usuario?

Recreando el inicio de sesión de todos los usuarios, de todas las bases de datos, de su instancia

Ya volví a crear el inicio de sesión, pero no tiene el mismo nombre de usuario. ¿Cómo vuelvo a crear la asociación Iniciar sesión x Usuario?

Ya hay un usuario creado. Quiero asociarlo con un nuevo inicio de sesión que crearé en tiempo de ejecución.

Se especificó un parámetro u opción no válido para el procedimiento 'sys.sp_change_users_login'

El usuario no puede reasignarse a un inicio de sesión. La reasignación solo se puede realizar para usuarios que fueron asignados a inicios de sesión de Windows o SQL.

MODIFICAR USUARIO… CON INICIO DE SESIÓN

Conclusión

Dirceu Resende

Comentários (0)