Arreglar usuarios huérfanos en SQL Server 2008 (Orphaned Login) tras migración
Cuando migramos bases de datos importando/exportando entre diferentes servidores Microsoft SQL Server es muy probable que nos encontremos con el problema de la imposibilidad de autenticar contra el servidor con los usuarios asignados a la base de datos. Aunque los usuarios estén bien creados en la instancia de SQL Server y asignados a la base de datos correspondiente, veremos que el atributo/campo “Login name” aparece vacío y sin posibilidad de modificarlo.
Lo primero que debemos hacer es verificar que es este el problema, para ello lanzaremos una query contra la base de datos que nos reportará los SID (security identifiers) que hay en la base de datos pero que no están asignados a ningún Login name/SQL Server Login:
USE <base_de_datos>;
GO;
sp_change_users_login @Action='Report';
GO;
Una vez detectados, podemos ir uno a uno arreglándolos:
USE <base_de_datos>; GO EXEC sp_change_users_login 'Auto_Fix', 'nombre_de_usuario'; GO
Y recibiremos el log indicando el fix del problema:
The row for user 'nombre_de_usuario' will be fixed by updating its login link to a login already in existence. The number of orphaned users fixed by updating users was 1. The number of orphaned users fixed by adding new logins and then updating users was 0.
Existe un script/Transact-SQL, disponible en el sitio web de Microsoft y que podéis ver a continuación que simplifica este proceso, permitiendo arreglar todos los login de una base de datos directamente. Como podéis ver, incorpora un switch en la variable autoFix que permite mediante TRUE/FALSE especificar si queremos únicamente un reporte del estado de la base de datos o si automáticamente reparar todos los Login. Antes de Microsoft SQL Server 2008 existía un procedimiento almacenado / stored procedure que permitía solventar este problema, pero está obsoleta (sys.sp_change_users_login)..
/* When you restore a database on a different SQL Server, all SQL users become a "orphaned user" because the SQL logins have a different SID then on the source server. In SQL Server 2005 you can fix this orphaned user with the stored procedure "sys.sp_change_users_login", but since version 2008 this SP is deprecated. This Transact-SQL statement also links orphaned user to existing principals with same name. In regard of security this script don't add a new principal if none with same name was found; as the SP with option AUTO_FIX do. Works with SQL Server 2005 and higher versions in all editions. Requires ALTER ANY USER permissions. Links: sys.sp_change_users_login: http://msdn.microsoft.com/en-us/library/ms174378.aspx ALTER USER: http://msdn.microsoft.com/en-us/library/ms176060.aspx */ -- Secure Orphaned User AutoFix DECLARE @autoFix bit; SET @autoFix = 'FALSE'; -- FALSE = Report only those user who could be auto fixed. -- TRUE = Report and fix !!! DECLARE @user sysname, @principal sysname, @sql nvarchar(500), @found int, @fixed int; DECLARE orphans CURSOR LOCAL FOR SELECT QUOTENAME(SU.[name]) AS UserName ,QUOTENAME(SP.[name]) AS PrincipalName FROM sys.sysusers AS SU LEFT JOIN sys.server_principals AS SP ON SU.[name] = SP.[name] AND SP.[type] = 'S' WHERE SU.issqluser = 1 -- Only SQL logins AND NOT SU.[sid] IS NULL -- Exclude system user AND SU.[sid] <> 0x0 -- Exclude guest account AND LEN(SU.[sid]) <= 16 -- Exclude Windows accounts & roles AND SUSER_SNAME(SU.[sid]) IS NULL -- Login for SID is null ORDER BY SU.[name]; SET @found = 0; SET @fixed = 0; OPEN orphans; FETCH NEXT FROM orphans INTO @user, @principal; WHILE @@FETCH_STATUS = 0 BEGIN IF @principal IS NULL PRINT N'Orphan: ' + @user; ELSE BEGIN PRINT N'Orphan: ' + @user + N' => Autofix possible, principal with same name found!'; IF @autoFix = 'TRUE' BEGIN -- Build the DDL statement dynamically. SET @sql = N'ALTER USER ' + @user + N' WITH LOGIN = ' + @principal + N';'; EXEC sp_executesql @sql; PRINT N' ' + @user + N' is auto fixed.'; SET @fixed = @fixed + 1; END END SET @found = @found + 1; FETCH NEXT FROM orphans INTO @user, @principal; END; CLOSE orphans; DEALLOCATE orphans; PRINT ''; PRINT CONVERT(nvarchar(15), @found) + N' orphan(s) found, ' + CONVERT(nvarchar(15), @fixed) + N' orphan(s) fixed.';
Comentarios recientes