Generate Script to Fix Orphaned Users in SQL Server Database

Fixing Orphaned Users in SQL Server Databases

Orphaned users can be a common issue in SQL Server databases, typically arising after database restores or server migrations. An orphaned user is a database user whose associated SQL Server login is missing or has a different SID (Security Identifier). This mismatch can lead to permission issues and hinder access to the database. In this blog, we’ll walk through how to identify and fix orphaned users using T-SQL.

Step 1: Identify Orphaned Users

To find orphaned users, we can query the sys.database_principals and sys.server_principals system views. The following script will list all orphaned users in the current database:

USE YourDatabaseName; -- Change to your database name
GO

-- Query to find orphaned users
SELECT dp.name AS OrphanedUser
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
  AND dp.type IN ('S', 'U'); -- 'S' for SQL user and 'U' for Windows user

This script checks for users in the database principals that don’t have a matching login in the server principals, indicating they are orphaned.

Step 2: Generate Commands to Fix Orphaned Users

After identifying the orphaned users, we need to generate the sp_change_users_login commands to fix them. The sp_change_users_login stored procedure can be used to map an existing database user to a login. The following script generates the necessary commands:

-- Declare a variable to hold the generated script
DECLARE @fixScript NVARCHAR(MAX);
SET @fixScript = '';

-- Cursor to iterate through each orphaned user
DECLARE user_cursor CURSOR FOR
SELECT dp.name AS OrphanedUser
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE sp.sid IS NULL
  AND dp.type IN ('S', 'U'); -- 'S' for SQL user and 'U' for Windows user

-- Variables to hold cursor output
DECLARE @orphanedUser NVARCHAR(255);

-- Open the cursor
OPEN user_cursor;

-- Fetch the first row
FETCH NEXT FROM user_cursor INTO @orphanedUser;

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Append the command to fix the orphaned user
    SET @fixScript = @fixScript + 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + @orphanedUser + ''', ''' + @orphanedUser + ''';' + CHAR(13) + CHAR(10);

    -- Fetch the next row
    FETCH NEXT FROM user_cursor INTO @orphanedUser;
END;

-- Close and deallocate the cursor
CLOSE user_cursor;
DEALLOCATE user_cursor;

-- Output the generated script
PRINT @fixScript;

This script uses a cursor to iterate through each orphaned user, generating a command to link the user to a login with the same name.

Step 3: Execute the Generated Script

After running the above script, you’ll get a list of sp_change_users_login commands to fix the orphaned users. Here’s an example of what the output might look like:

EXEC sp_change_users_login 'UPDATE_ONE', 'testuser', 'testuser';

To fix the orphaned users, execute each command in the output:

EXEC sp_change_users_login 'UPDATE_ONE', 'testuser', 'testuser';
GO

Additional Notes

• Ensure that the corresponding logins exist on the SQL Server. If a login does not exist, create it first using CREATE LOGIN:

  CREATE LOGIN testuser WITH PASSWORD = 'yourpassword';

• It’s crucial to have appropriate permissions to execute these commands.

Conclusion

Orphaned users can disrupt access and permissions within your SQL Server databases. By identifying these users and linking them to the appropriate logins, you can restore proper access and functionality. The provided T-SQL scripts streamline the process, making it easier to maintain your database integrity post-migration or restoration.