How to Fix Orphaned Users in SQL Server

Introduction

SQL Server separates the concept of login and user. A login has the main purpose of allowing to connect to the SQL Server instance itself. However, by default a login cannot connect to any (non-system) database. Database access is controlled by users. A user is defined within the database. That allows easier management of database permissions and allows for those permissions to be included in a database backup. To allow a login access to a database, the login must be associated with a user in that same database.

Sometimes that login-user association breaks. That can for example happen, when a backup is restored on a different server. In this article, we are going to look at why that disassociation happens. We are also going to find out how to fix such an orphaned user.

Login User Association Example

To start out we need a login and an associated user:

By adding the FROM LOGIN clause to the CREATE USER statement, we specify that the new user should be linked to that particular login. While it is a good practice to name logins and their associated users identically, it is not a requirement. You can link a new user to any existing login by specifying that login in the FROM LOGIN clause, even if it has a different name than the new user.

To show that the association worked, you can run the following code snippet:

In here, EXECUTE AS is used to switch the security context to the TestPrincipal login. USER_NAME on the other hand returns the name of the current user. When executing the above snippet, you will get this output:

It shows that the TestPricipallogin and the TestPrincipal user are indeed currently associated.

Creating an Orphaned User

Now let us see what happens, when that association is broken. Instead of taking a backup and restoring it on a different server, we are going to just drop the login and recreate it. That has basically the same effect on the inner linkage structure.

When we now try to execute as that login, while still being connected to the database that we created the user in, we get an error right away:

The TestPrincipal login does not have an associated user in the database anymore. As the TestPrincipal user still exists, this proves that the linkage is not established by the name. It is instead established using the SID. SID stands for security identifier and it is a GUID. A login and a user are associated with each other if they have the same SID. A user the does not have an associated login anymore is called an orphaned user.

SIDs are also used by Windows. Every Windows account has a unique SID. If you create a Windows login in SQL Server, it assigns the same SID to it that is used for that account in Windows already.

How to Fix Orphaned Users

So, how can we see if a login and a user in SQL Server have the same SID? Both, the sys.server_principals and the sys.database_principals catalog views have an SID column:

When you execute that query, you will get the following output:

You can clearly see that the two SIDs are not identical. To fix that and get the orphaned user to have the same SID as "its" login again, we just need to remind SQL Server that this was indeed our intend, by running the following ALTER USER statement:

After executing that statement, we can run above SID-comparing query again:

Note that the SID of the user changed. It is now the same as the login's SID again.

Just to make sure that this did correctly associate the two principals with each other, let us run our first query again:

Summary

SQL Server separates logins and database users. Logins control access to the instance itself. Users on the other hand control access to a single database. For a login to be able to access a database, it needs to have an associated user in that database. That association is implemented using the SID. When the SID on a login and a user match, SQL Server considers them linked. If that link should ever break, you can repair it with the ALTER USER statement, specifying the WITH LOGIN clause.