Auto Fix Orphaned Users

2014-04-18 - General, Security

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:

  FROM sys.database_principals AS DP
  LEFT JOIN sys.server_principals AS SP2
    ON DP.sid = SP2.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:

  FROM sys.database_principals AS DP
  LEFT JOIN sys.server_principals AS SP
    ON DP.name = SP.name COLLATE SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN sys.server_principals AS SP2
    ON DP.sid = SP2.sid

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:

  FROM sys.database_principals AS DP
  LEFT JOIN sys.server_principals AS SP
    ON DP.name = SP.name COLLATE SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN sys.database_principals AS DP2
    ON SP.sid = DP2.sid AND DP.principal_id <> DP2.principal_id
  LEFT JOIN sys.server_principals AS SP2
    ON DP.sid = SP2.sid

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:

WHERE DP.authentication_type_desc IN ('INSTANCE','WINDOWS')
  AND DP.principal_id>1

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:

SELECT DP.name, DP.type_desc,
       CASE WHEN SP.sid IS NOT NULL THEN 1 ELSE 0 END has_name_match,
       CASE WHEN SP.sid = DP.sid THEN 1 ELSE 0 END is_name_sid_matched,
       CASE WHEN SP2.sid IS NOT NULL THEN SP2.name END has_sid_match,
       CASE WHEN dp2.sid IS NOT NULL THEN 1 ELSE 0 END name_sid_conflict
  FROM sys.database_principals AS DP
  LEFT JOIN sys.server_principals AS SP
    ON DP.name = SP.name COLLATE SQL_Latin1_General_CP1_CI_AS
  LEFT JOIN sys.database_principals AS DP2
    ON SP.sid = DP2.sid AND DP.principal_id <> DP2.principal_id
  LEFT JOIN sys.server_principals AS SP2
    ON DP.sid = SP2.sid
WHERE DP.authentication_type_desc IN ('INSTANCE','WINDOWS')
  AND DP.principal_id>1

You can see an example result of this query below:

List of orphaned users.

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:

WHERE has_name_match = 1
   AND has_sid_match IS NULL
   AND name_sid_conflict = 0

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

SELECT 'ALTER USER '+QUOTENAME(name)+' WITH LOGIN '+QUOTENAME(name)+';' AS cmd

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:

SELECT 'ALTER USER '+QUOTENAME(OU.name)+' WITH LOGIN '+QUOTENAME(OU.name)+';' AS cmd
  FROM(
       SELECT DP.name, DP.type_desc,
              CASE WHEN SP.sid IS NOT NULL THEN 1 ELSE 0 END has_name_match,
              CASE WHEN SP.sid = DP.sid THEN 1 ELSE 0 END is_name_sid_matched,
              CASE WHEN SP2.sid IS NOT NULL THEN SP2.name END has_sid_match,
              CASE WHEN dp2.sid IS NOT NULL THEN 1 ELSE 0 END name_sid_conflict
         FROM sys.database_principals AS DP
         LEFT JOIN sys.server_principals AS SP
           ON DP.name = SP.name COLLATE SQL_Latin1_General_CP1_CI_AS
         LEFT JOIN sys.database_principals AS DP2
           ON SP.sid = DP2.sid AND DP.principal_id <> DP2.principal_id
         LEFT JOIN sys.server_principals AS SP2
           ON DP.sid = SP2.sid
       WHERE DP.authentication_type_desc IN ('INSTANCE','WINDOWS')
         AND DP.principal_id>1
      )OU
 WHERE OU.has_name_match = 1
   AND OU.has_sid_match IS NULL
   AND OU.name_sid_conflict = 0

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:

Generated auto fix orphaned user statements.

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.

Categories: General, Security
Tags: , , , , ,