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.

If you prefer a T-SQL script, you could also try this solution by the Lone DBA.

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 with a lot of databases and a lot of orphaned users all over the place?

What if this is a commonly recurring problem for your organization or customers?

Would you want to 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:

See the gist on github.

The script takes into consideration the following possible scenarios:

1. Login exists with the same name as the orphaned user 👉 Generate ALTER USER to map the user to the login.
2. Login with a different name but the same sid exists 👉 Generate ALTER LOGIN to map the user to the login.
3. Login SID is identifiable but login doesn’t exist in SQL 👉 Generate CREATE LOGIN FROM WINDOWS to create a Windows authentication login.
4. No login exists with the same name as the orphaned user 👉 Generate DROP USER command to drop the orphaned user. If the user owns schemas, all of them will be transferred to the dbo user – or dropped (based on specified parameters @DropEmptyOwnedSchemas and @DropOwnedObjects).
5. The orphaned user is actually dbo (the database owner) 👉 Change the database owner to sa (or however it’s called in your instance).

The script also contains several “parameters” at the top to control its behavior.

Feel free to use the above scripts for your convenience!

Permissions

Any login can see their own login name, the system logins, and the fixed server roles. To see other logins, requires ALTER ANY LOGIN, or a permission on the login.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

To change the target login of a user requires the CONTROL permission on the database.

To transfer schema ownership requires the TAKE OWNERSHIP permission on the database. If the new owner is not the user that is executing this statement, also requires IMPERSONATE permission on the sa login.

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