Obligatory Orphan-Related Imagery

T-SQL Script to Fix Orphaned DB Users Easily

What are Orphaned Users

“Orphaned Users” is a common issue in SQL Server where a Database User is no longer associated with its relevant Server Login.

This often happens when the Server Login is deleted (even if it’s recreated later), or when the database is moved or restored to a different SQL Server.

You can find some more info on it in this article from Microsoft.

The symptoms that you can expect from such a scenario is inability to use such database users. Specifically, using the login to connect to the database that it’s supposed to be mapped to. It just doesn’t work.

You’ll receive the following error:

Login failed for user ‘MyUser’. (Microsoft SQL Server, Error: 18456)

The Login could exist on the server level. The User also exists within the relevant database. But it doesn’t work because the SID that the database user is mapped to, is different from the SID of the actual Server Login.

This is the real problem, because Users are mapped to their Logins by their SID, not by their names… And every time you create a new Login, it receives a new, random SID.

The “Correct” Solution

The most correct solution for this problem, is to have consistent SIDs to your Logins across all your SQL Servers. So that even when a database is moved to a different server, it could still use the same SID that it was originally created for.

And also, when you recreate a previously deleted Login, you’d need to create it with the same SID that it originally had.

This is, obviously, not a trivial matter, and not always possible. But if this is a direction that interests you, then you will find the following very useful:

A long time ago, Microsoft created a special procedure for the purpose of migrating logins from one server to another.

This procedure outputs the CREATE script for a specified login (or all logins), by retaining its original SID and even its password (if it’s a SQL Login).

They called it “sp_help_revlogin” and published this article to explain how to use it.
Even though this is very, very, very old, they still only provide it till this day as a “downloadable” script instead of a built-in system procedure.

One drawback of this procedure, though, is that it only provides the creation script for the Login itself, but not for its permissions, roles, etc.

If you ever need to migrate Logins from one SQL Server to another, including their roles and permissions and such, I recommend the easy-to-use Powershell library “dbatools” which contains the command Copy-DbaLogin. More info here.

The Easy Solution

Orphaned Users are nothing new in SQL Server.
That’s why the (now deprecated) system procedure sp_change_users_login exists since, about, forever.

You can use it with parameter @Action = ‘Auto_Fix’ and it’ll automatically remap an orphan database user to a Login with the same name, if such exists.
But this procedure (and even its modern counterpart ALTER USER) only affects a single Login at a time.

So, what if you have A LOT of such orphaned users?

Even worse, what if you have a lot of servers and a lot of orphaned users in each?

What if this is a commonly recurring problem within your organization?
Will you write the same command every time for every user?
I wouldn’t.

I would prefer to write a T-SQL script that will do it for me.

Kinda like the following script, which I happen to have written a while ago, and can be used to reconnect all users within the current database:

 

SET NOCOUNT ON;
DECLARE @user NVARCHAR(MAX);

DECLARE Orphans CURSOR FOR
SELECT 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 authentication_type_desc = 'INSTANCE'
AND dp.name IN (SELECT name FROM sys.server_principals);

OPEN Orphans
FETCH NEXT FROM Orphans INTO @user

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX);
SET @Command = N'ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
PRINT @Command
EXEC (@Command);

FETCH NEXT FROM Orphans INTO @user
END

CLOSE Orphans
DEALLOCATE Orphans

Or like this script, which can reconnect all users to their logins for ALL of the databases on the server:

SET NOCOUNT ON;
DECLARE @db SYSNAME, @user NVARCHAR(MAX);
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX));
exec sp_MsforEachDB '
INSERT INTO #tmp
SELECT ''?'', 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 authentication_type_desc = ''INSTANCE''
AND dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals);'

DECLARE Orphans CURSOR FOR
SELECT DBName, UserName FROM #tmp;

OPEN Orphans
FETCH NEXT FROM Orphans INTO @db, @user

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX)
SET @Command = N'USE ' + QUOTENAME(@db) + N'; ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
PRINT @Command;
EXEC (@Command);

FETCH NEXT FROM Orphans INTO @db, @user
END

CLOSE Orphans
DEALLOCATE Orphans

You can also find these scripts in my GitHub Gist here:
https://gist.github.com/EitanBlumin/e6ad247bd93923dc3ea5edaba8ab1e81

Feel free to use the above scripts for your convenience!

This article was originally published by Eitan Blumin on January 2018, at www.madeiradata.com

One thought on “T-SQL Script to Fix Orphaned DB Users Easily

  1. Here is an updated version of the instance-wide script, which takes into consideration the following scenarios:

    1. Orphan user is “dbo”, which means database has no owner -> Sets the owner to the sa login.
    2. Orphan user has a matching login -> Generates ALTER USER command to match the login.
    3. Orphan user has no matching login -> Generates DROP USER command, plus ALTER SCHEMA to move ownership of any owned schemas to dbo.

    SET NOCOUNT ON;
    DECLARE @db SYSNAME, @user NVARCHAR(MAX), @loginExists BIT, @saName SYSNAME, @ownedSchemas NVARCHAR(MAX);
    SELECT @saName = [name] FROM sys.server_principals WHERE sid = 0x01;

    IF OBJECT_ID(‘tempdb..#tmp’) IS NOT NULL DROP TABLE #tmp;
    CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX), LoginExists BIT, OwnedSchemas NVARCHAR(MAX));
    exec sp_MsforEachDB ‘
    INSERT INTO #tmp
    SELECT ”?”, dp.name AS user_name
    , CASE WHEN dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals) THEN 1 ELSE 0 END AS LoginExists
    , OwnedSchemas = (
    SELECT ”ALTER AUTHORIZATION ON SCHEMA::” + QUOTENAME(sch.name) + N” TO [dbo]; ”
    FROM [?].sys.schemas AS sch
    WHERE sch.principal_id = dp.principal_id
    FOR XML PATH (””)
    )
    FROM [?].sys.database_principals AS dp
    LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
    WHERE sp.SID IS NULL
    AND authentication_type_desc = ”INSTANCE”;’

    IF EXISTS (SELECT NULL FROM #tmp)
    BEGIN
    DECLARE Orphans CURSOR FOR
    SELECT DBName, UserName, LoginExists, OwnedSchemas FROM #tmp;

    OPEN Orphans
    FETCH NEXT FROM Orphans INTO @db, @user, @loginExists, @ownedSchemas

    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @Command NVARCHAR(MAX)

    IF @user = ‘dbo’
    SET @Command = N’USE ‘ + QUOTENAME(@db) + N’; ALTER AUTHORIZATION ON DATABASE::’ + QUOTENAME(@db) + N’ TO ‘ + QUOTENAME(@saName) + N’ — assign orphaned [dbo] to [sa]’
    ELSE IF @loginExists = 0
    SET @Command = N’USE ‘ + QUOTENAME(@db) + N’; ‘ + ISNULL(@ownedSchemas, N”) + N’ DROP USER ‘ + QUOTENAME(@user) + N’ — no existing login found’
    ELSE
    SET @Command = N’USE ‘ + QUOTENAME(@db) + N’; ALTER USER ‘ + QUOTENAME(@user) + N’ WITH LOGIN = ‘ + QUOTENAME(@user) + N’ — existing login found’

    PRINT @Command;
    –EXEC (@Command);

    FETCH NEXT FROM Orphans INTO @db, @user, @loginExists, @ownedSchemas
    END

    CLOSE Orphans
    DEALLOCATE Orphans
    END
    ELSE
    PRINT N’No orphan users found in instance!’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s