Addressing orphaned users is an important piece of SQL Server security management. When left unchecked, you can accumulate an overwhelming number of users that exist in a database but do not have a correlating login in the master database, thus preventing access to the server or the database at all. There are dbatools PowerShell modules out there that can help you accomplish what sp_FindOrphanedUser does. However, there are some situations where PowerShell may not be available to you for a multitude of reasons, and having an extra tool in the toolbelt never hurt anyone, am I right?
What is an Orphaned User?
An orphaned user, according to Microsoft, occurs “when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist.” So, if you aren’t using groups, and you explicitly add a login to a SQL Server, then later delete that login, the user will still exist in the database, thus orphaning it from its login. Likewise, if you perform a database migration and do not bring over all logins, the associated users in the databases will remain within the database but will be orphaned if their login is not brought over as well.
Another Kind of Orphaned User
Sometimes, you may find yourself in a scenario where a login is able to connect to a SQL Server, but it cannot open a database it is mapped to. This can happen when the database user’s SID and the associated login SID do not match. You may have solved this issue in the past by using sp_help_revlogin. Using sp_FindOrphanedUser can help remediate all of the above scenarios.
What Does sp_FindOrphanedUser Do?
There are three checks sp_FindOrphanedUser does:
- Checks for orphaned users that do not have an associated login in master.
- Checks for orphaned users that do have an associated login in master, but their SIDs do not match.
- Checks for any schemas owned by orphaned users that do not have an associated login in master.
The reason for the last check is sometimes, throughout user creation, they may have at one time or another owned a schema. In order to drop a user in a database, they cannot own any schemas. This scans for that and gives you the syntax to correct it to allow you to drop the orphaned user, should you choose to do so.
How to Run sp_FindOrphanedUser
There are two ways you can run sp_FindOrphanedUser:
- Without any parameters.
- With @DatabaseName
By default, sp_FindOrphanedUser is designed to scan all databases on a server and return its output. However, in the event you want to get more granular with your analysis and cleanup, you can specify a database name in the @DatabaseName parameter.
Putting sp_FindOrphanedUser to the Test
In the following examples, I will have three orphaned users:
- OrphanedUser1
- OrphanedUser2
- OrphanedUser3
OrphanedUser1 will be your classic orphaned user. Someone decided that this user is no longer needed, and they deleted the login but left the user in the database. OrphanedUser2 will be an example of where the user exists in a database, and the login exists as well in master, but the SIDs do not match, therefore preventing that user from opening the required database. OrphanedUser3 will be a classic orphaned user, but will also own a schema, and that schema ownership needs to change before you can drop the orphaned user.
OrphanedUser1 Example
So here is our classic example of an orphaned user. Someone deletes a login, but the user still exists in a database. When I run sp_FindOrphanedUser, without any parameters, I get the following:
As you can see, we have more than one orphaned user at this time on the server in different databases. Let’s narrow down our output by providing a database name for the @DatabaseName parameter:
Now we see just the output of the one database we provided. Looking at the output above, we see several columns that provide information about the orphaned user, as well as three columns I’d like to highlight. The first column to touch on is the Report column. Most of the time, you aren’t just running through production dropping users. This column gives you a brief writeup you can use to quickly provide insight into what you found and what you plan to do to correct the orphaned user. In our case, here’s what the output of the Report column looks like:
[OrphanedUser1] is an orphaned user in the [StackOverflow2013] database. This means that they do not have a corresponding server login. The orphaned user should be repaired or dropped. The next column to touch on is the repair_orphaned_user column. This is generated code that provides you with some options to repair the orphaned user, should you need to. Let’s look at the output for our example:
CREATE LOGIN [OrphanedUser1] WITH PASSWORD = ' ' / FROM WINDOWS USE [StackOverflow2013] ALTER USER [OrphanedUser1] WITH Login = [OrphanedUser1]
There are two options for repair: WITH PASSWORD and FROM WINDOWS. WITH PASSWORD should be used if it is a SQL Authentication login, and FROM WINDOWS should be used if it is a Windows Authentication login. The last column to take note of is the drop_orphaned_user column. Again, this is generated code that you can copy/ paste to another query window to quickly drop the orphaned user. Let’s take a look at this output:
USE [StackOverflow2013] DROP USER [OrphanedUser1]
Having the database context in the generated code allows you to quickly drop the user should you choose to do so.
OrphanedUser2 Example
In this example, we find the user and login OrphanedUser2 existing in a user database, as well as in the master database, so there is an associated login with a matching name, but their SIDs do not match. By running sp_FindOrphanedUser @DatabaseName = ‘StackOverflow2013’, we find the following:
We get the same output in result set one just like for the first example, but now we also get a second result set. In this case, sp_FindOrphanedUser found our SID mismatch situation. I had forced this to happen by changing the SID of the OrphanedUser2 login to end with a 4 instead of a 3, so their SIDs no longer match. We get an easy-to-use script in result set two. Let’s take a look at it:
USE [StackOverflow20132013] EXEC sp_change_users_login 'UPDATE_ONE','OrphanedUser2','OrphanedUser2'
The column link_to_existing_login provides you with a quick and easy way to link the database user to an existing login. By running the above, the user can now access the StackOverflow2013 database.
OrphanedUser3 Example
This last example showcases how sp_FindOrphanedUser works when you need to drop an orphaned user, but the user in the database owns a schema. When running sp_FindOrphanedUser in this scenario, we get the following:
It looks like OrphanedUser3 is:
- An orphaned user in the StackOverflow2013 database.
- Owns a schema called OrphanedUser3Schema.
If we just try to drop the user using the drop_orphaned_user syntax in the first result set, we get the following error:
This is where the third result set comes in handy. Result set number three gives us the following in the AlterAuthorization column:
USE [StackOverflow2013] ALTER AUTHORIZATION ON SCHEMA::[OrphanedUser3Schema] TO [dbo]
This changes the owner of that schema to dbo and allows you to drop the user without an issue.
Summary
All in all, managing orphaned users in your environment is a key component of security as it relates to SQL Server. Using sp_FindOrphanedUser gives you easy insight and quick resolutions to getting a handle on orphaned users in your environment.
You can download sp_FindOrphanedUser from the Straight Path Solutions GitHub repository.
