Auto Fix Orphaned Users

Introduction

Over the last few days we talked about how to copy logins from one instance of SQL Server to another. We also talked about how to fix orphaned users that lost their association with the underlying login, for example because the database was restored on a different server.

Today I would like to show you a T-SQL query that you can use to generate the statements necessary to auto fix orphaned users.

Identifying Fixable Orphaned Users

In SQL Server, a login is associated with a database user if (and only if) their SIDs match. If that link is severed, there is no way to tell, which user belongs to which login. That makes automatically fixing this link a little difficult.

It is however considered a best practice to give your logins and their users the same name. If you follow that practice, you can use the name to identify which login to link to which user. While that is not a perfect solution, it will work in most cases. Therefore that is the solution I am going with in this article.

Let us begin by figuring out when a user needs to be re-linked and what would stop us from doing that automatically. A user is linked to a login if their SIDs match. That means in turn that a user needs to be considered for auto fixing, if there is no login with the same SID. To find those users we can (left) join sys.database_principals with sys.server_principals on the SID:

No users that have a matching login already need to be looked at any further. The others however might, as they are orphaned users.

We said before that we are going to use the name to find match candidates. Not every user will have a candidate login however. To find out, we are going to have to left join to sys.server_principals again, this time on the name:

Depending on your collation settings, this join might cause a collation conflict. I added the COLLATE clause to prevent that.

In SQL Server a login can be associated with at most one database user in any given database. That means, if the candidate login we just found by looking at the name is already associated with another user in our database, we cannot use it. To identify those conflicting logins we need to left join to sys.database_principals one more time:

There are database principals that we do not want to mess with. For example the dbo user. dbo always has the principal_id 1 so it is easy to identify. We also have users that cannot be linked to a login. Examples are users that where created WITHOUT LOGIN or users that use database authentication in a partially contained database.

The users that can be linked can be identified by their authentication type, which needs to be either INSTANCE or WINDOWS. With that we can add the following WHERE clause to our query:

Now we have filtered fixable users and we have joined to a bunch of other CVs. The next step is to combine the data from all tables to something meaningful:

You can see an example result of this query below:

The query returns one row for each user in the current database. The has_name_match column indicates if there is a login with the same name. Similarly, has_sid_match indicates if a login with the same SID exists. This column however is not 0 or 1 but instead contains the name of the login if it exists and NULL otherwise.

is_name_sid_matched equals 1 if the same login matches our user in name and SID. name_sid_conflict finally is 1, if the login that matches in name is already associated with another user in this database.

The Auto Fix Orphaned Users Query

Now we can identify users that need fixing and that also have a suitable login candidate with this WHERE clause:

The only step that remains is now to create the actual ALTER USER statement:

The expression uses the QUOTENAME function to make sure that the generated statements work with oddly named users too.

Now it is time to put all the pieces together:

This query takes all the rules we talked about into consideration and generates auto-fix statements for just those orphaned users that can be safely re-associated with an unambiguous login. The output in my case looks like this:

Final Thoughts

While it sounds simple, identifying the correct login to associate with an orphaned user is not completely trivial. But it can be done if you follow a few simple rules. The query that we came up with can be used to generate the statements necessary to auto fix orphaned users. However, it just generates those statements. Actually executing them I will leave to you as an exercise.