Introduction

Context

As SQL Server database administrators, we should all know that, most of the time, a database user is linked to a SQL Server login. We do this to tell SQL Server that a SQL login LoginA has access to database Db1 using the context and permissions of UserA database user.

There are exceptions like the two following ones:

1. LoginA has very high permissions at server level like being member of sysadmin role
2. Db1 is self-contained, which means that it uses its own authentication and does not deal with SQL Logins. (SQL Server 2012+)

We might think that this linkage is done by matching names, but it’s not the case. Normalization comes in and this mapping is actually stored using a unique identifier called “SID”:

We can check SIDs for all our server principals using the following query :

If we look at documentation page of the sys.server_principals view, we can see that :

• The principal_id is like an identity column. So, if we drop a login and recreate it, the principal_id won’t be the same.
• The sid column defines a security identifier for the principal that will be the same for Windows authenticated logins. This means that if we use mixed authentication and we create a login then drop and create it again, it won’t have the same SID.

If a mapping is defined for a given SQL Login, then its corresponding SID can be found in sys.database_principals using the following query:

For simplicity, it’s common, but not mandatory, to use the exact same name for both SQL Login and Database User.

As you could expect, this linkage can be “broken” in many different ways that makes this definition “inconsistent”, that leaves database users without any link to a login. When this happens, we refer to such a database user as an orphaned database user.

The situation is summarized in following figure.

Let’s enlist some use cases that lead to orphaned database users:

• Dropping a SQL Login using SSMS, even though we are well aware that this will happen as SSMS shows a warning:

  

• When using mixed authentication mode, restoring a database backup to another SQL Server instance.
• After a database mirroring failover.

As you can see above, there are two possible ways to manage these orphaned users:

1. Drop them as they are no longer needed
2. Link them back to the SQL Server login with the appropriate name

In the following sections, we will see how to list those orphaned database users and how to handle both cases listed above. Finally, we will review stored procedures that will be helpful in that situation.

Orphaned Database User discovery

To be able to get a list of orphaned users for every databases of a given SQL Server instance, you have to run the following statement against each of them:

This stored procedure will return a two-columns dataset with firstly the name of an orphaned database user and secondly its corresponding security identifier.

Alternatively, we can get the list of SIDs that are defined in sys.database_principals but not in sys.server_principals as follows:

Handling an orphaned database user

As we said previously, there are two ways to handle orphaned database users: either we drop or remap them.

Database User Drop

Dropping a database user seems pretty straight forwards: we simply need to run the DROP USER statement and it’s over. But what if this database user owns objects inside the database? It’s not that simple…

If it’s really the action we want to perform, we will need not only to take a look at that aspect and correct it, but also to check that this user is not used as the execution context of one or more stored procedures, functions or event notifications. Here is the error we could get if that’s the case:

Msg 15136, Level 16, State 1

We will also need to remove group membership of that user and there are maybe some other aspects that I don’t cover it because I’ve never been confronted to them.

An analysis of these aspects can be quite long and has to be done for all orphaned users. Plus, this action can be used in other database management tasks. So, this is a good candidate for automation and you will find attached to this article a script with a stored procedure that takes care of dropping a database user and performs some pre-checks before that. The script is called

Let’s review the details of this stored procedure.

First, let’s see its parameters

As you can see, it takes the name of the database in which we want to perform the drop and the name of the database user we want to drop. There is a flag bit to tell the procedure to assign database objects owned by @UserName to another database user @NewObjectOwner. By default, this user is dbo. Finally, there is a flag bit that will enable a debug mode and make this procedure more talkative.

The first part of the stored procedure is a set of pre-requisities checks :

• Check that database exists
• Check that database user exists
• Check that the object owner for @UserName’s object exists in the database, only if @ResetOwnership parameter is set to 1.
• Check that the database user we want to drop is not a MS-Shipped user like dbo or INFORMATION_SCHEMA.
• Check modules with an EXECUTE AS statement that refers to the database user we want to drop. If any module is found, it’s returned by a SELECT statement.

Then it will get the list of owned schemas and owned roles and raise an error whenever @ResetOwnership parameter is not set to 1 and it founds something.

Finally it will stack roles of which @UserName is member.

Once the list is complete, it will loop and either :

• Put an end to role membership using sp_droprolemember stored procedure
• Transfer ownership of schema or role using an ALTER AUTHORIZATION statement

When all that is done, we can finally run the DROP USER statement.

Database User Remapping

For remapping, we must use the sp_change_users_login stored procedure we already used to report orphaned users.

We can run it for a single database user mapping as follows:

Alternatively, we can try the autofix parameter :

But running this requires that the name of SQL Login to be the exact same as the database user we want to remap.

This procedure also allows to create the login when it doesn’t exist.

In any of the methods presented above, this has to be performed one database at a time.

Instance-level management of orphaned users

Components of the solution

In the previous section, we saw how to manage orphaned users for one database at a time. While this is good for testing purpose, we might think that it’s not affordable for production environments where we sometimes have 10 or more databases that have been restored and that we must take care of.

That’s the reason why I developed a stored procedure called Administration.FindOrphanUser that will loop across all databases of the instance and optionally try to fix orphaned users (which means remapping) and/or drop them.

It’s built on a stored procedure called Common.RunQueryAcrossDatabases that runs a T-SQL statement across a set of databases. It’s like the undocumented sp_MSforeachdb stored procedure, but with its differences.

Both Administration.FindOrphanUser and Common.RunQueryAcrossDatabases procedures are attached to this article.

As it’s not the aim of this article, I won’t insist on Common.RunQueryAcrossDatabases procedure and we will focus on the other one.

So, what’s its interface? It takes 5 parameters:

• an optional database name
• a flag bit to tell whether or not try to remap database users to a login of the same name
• a flag bit to tell whether or not try to drop remaining orphaned database users (after the trial to remap has been done)
• a parameter to tell whether to get the list of orphaned users or an execution report. Two possible values then: ‘REPORT’ or ‘TABLE’. All other values than these ones are equivalent to a ‘NONE’ output value and no dataset will be returned by the stored procedure.

This gives the following in T-SQL:

This stored procedure will create a temporary table #OrphanUsersData that will contain all the information (and a little more) that are manipulated during execution.

Here is the creation statement for this table.

Let’s review each column.

We have obviously columns for database name and user name. Then we have some general information on that user:

• The UserType column should be either SQL_USER or WINDOWS_USER and corresponds to the type_desc column in sys.database_principals.
• The name of the default schema. It’s true we didn’t mention it previously, but each database user has a default schema (which is, in my experience and unfortunately most of times dbo).
• Its creation and last modification dates

The next two columns are related to the execution of Administration.FindOrphanUser stored procedure. It will contain the outcome of the execution that is implied by @TryToFix and @DropUnfixable parameters.

Finally, we have the last two columns that will contain DDL statements. The first one will be used to drop the database user (it will actually be a call to former [Administration].[DropDatabaseUser] stored procedure). The second one will contain the statement for database user to login re-mapping. As this statement is pretty simple, we created it as a computed column.

This table will be used as destination in the query we will provide to Common.RunQueryAcrossDatabases stored procedure. Actually, this query will be formed as follows:

Once this stored procedure has completed the discovery of orphaned database users, if @TryToFix parameter is set to 0, then it wil loop on each database user and run the corresponding DDL2Remap statement. The outcome of this operation will be stored in FixIssue column.

Same goes for the database user removal if @DropUnfixable is set to 1.

Finally, the stored procedure returns a dataset that is either the content of #OrphanUsersData temporary table or a report based on values of FixIssue and DropIssue columns.

How to install?

Prior to installation, you must first select or create a database in which you will install this solution. You must also create following database schemas:

• Common
• Administration

You will find below the list of files that are attached to this article and must be run in that order.

• Type.Common.DatabaseNameList.sql
• Procedure.Common.RunQueryAcrossDatabases.sql
• Procedure.Administration.DropDatabaseUser.sql
• Procedure.Administration.FindOrphanUsers.sql

How to use the solution?

This is, to me, the best way to manage orphaned users. You will find below the way I recommend to use these stored procedures.

First, run with defaults. This will just list all orphaned users with everything we need to fix the situation.

This call should also be automated so that a notification is sent to database administrators when one or more orphaned users are found. This automation could also be used by checking the TotalCount column of the dataset returned by following call:

If some orphaned users are reported, then we can run the procedure with @TryToFix parameter set to 1:

If any error is returned by the procedure, check it and try to solve. Don’t hesitate to send me a message with details of the error so that I can try to update these stored procedures to the situation you found.

I don’t recommend running with @DropUnfixable parameter set to 1 if you are not sure of what you are doing.

• Author
• Recent Posts

Jefferson Elias

Living in Belgium, I obtained a master degree in Computer Sciences in 2011 at the University of Liege.

I'm one of the rare guys out there who started to work as a DBA immediately after his graduation. So, I work at the university hospital of Liege since 2011. Initially involved in Oracle Database administration (which are still under my charge), I had the opportunity to learn and manage SQL Server instances in 2013. Since 2013, I've learned a lot about SQL Server in administration and development.

I like the job of DBA because you need to have a general knowledge in every field of IT. That's the reason why I won't stop learning (and share) the products of my learnings.

View all posts by Jefferson Elias

Latest posts by Jefferson Elias (see all)

• How to perform a performance test against a SQL Server instance - September 14, 2018
• Concurrency problems – theory and experimentation in SQL Server - July 24, 2018
• How to link two SQL Server instances with Kerberos - July 5, 2018