SCRIPT: Fix orphan users T-SQL script

-- SQL Server Fix Orphaned Database Users Script
-- Reconnects orphaned database users to existing server logins
-- Orphaned users occur after database restore/attach operations
 
-- =============================================
-- CONFIGURATION
-- =============================================
USE [KL4_prod_restored];
GO
 
SET NOCOUNT ON;
 
-- =============================================
-- Variable Declarations
-- =============================================
DECLARE @username NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE @loginExists INT;
 
-- =============================================
-- Find Orphaned Users
-- =============================================
DECLARE user_cursor CURSOR FOR
SELECT dp.name
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')  -- SQL user, Windows user, Windows group
      AND dp.sid IS NOT NULL
      AND dp.sid NOT IN (0x00, 0x01)
      AND sp.sid IS NULL  -- Login doesn't exist (orphaned)
      AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'MS_DataCollectorInternalUser');
 
-- =============================================
-- Remap Orphaned Users to Logins
-- =============================================
OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO @username;
 
WHILE @@FETCH_STATUS = 0
BEGIN
      SELECT @loginExists = COUNT(*)
      FROM sys.server_principals
      WHERE name = @username;
 
      IF @loginExists > 0
      BEGIN
            BEGIN TRY
                  SET @sql = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']';
                  EXEC sp_executesql @sql;
            END TRY
            BEGIN CATCH
                  -- Silent error handling
            END CATCH
      END
 
      FETCH NEXT FROM user_cursor INTO @username;
END;
 
-- =============================================
-- Cleanup
-- =============================================
CLOSE user_cursor;
DEALLOCATE user_cursor;
 
SET NOCOUNT OFF;