Removing Orphaned SQL Users

I haven’t come across issues with orphaned users too often.  When I have, it’s typically been users that are obviously no longer needed and can be removed rather than fixed.  Below is an example of one such scenario I’ve encountered.  First, let’s setup our test in the StackOverflow2013 database and since I’m in a Marvel comics mood (welcome to the Marvel Cinematic Universe Daredevil!) let’s say we confiscated a database that had previously been accessed by Kingpin:

USE [master]
GO
CREATE LOGIN [Kingpin] WITH PASSWORD=N'YouWontGuessThisOne'
GO
USE [StackOverflow2013]
GO
CREATE USER [Kingpin] FOR LOGIN [Kingpin]
GO
CREATE SCHEMA [Kingpin] AUTHORIZATION [Kingpin]
GO
ALTER ROLE [db_owner] ADD MEMBER [Kingpin]
GO

The script above will create the login, user, and give db_owner rights to Kingpin.  You may have also noticed the “CREATE SCHEMA” line.  More on that later.  Let’s take a look at a couple pieces of information on the new user:

SELECT dbp.name, dbp.sid as 'dbp_sid', sp.sid as 'sp_sid'
FROM sys.database_principals AS dbp
LEFT JOIN sys.server_principals AS sp ON dbp.SID = sp.SID
WHERE dbp.name = 'Kingpin'

Notice the SID for the user in both sys.database_principals and sys.server_principals.  The values above match.  If there are not matching values, we have a problem.  What happens if the Kingpin login gets dropped?

If we run the select statement again after dropping the Kingpin login, we’ll see that the SID from sys.server_principals now shows up as NULL:

We can also run the following to check the database for orphaned users and confirm that Kingpin is orphaned:

EXEC sp_change_users_login 'Report';

Much like when I encountered this recently, I know we can safely drop the Kingpin user.  He’s in prison and when a super villain is in prison they never, ever get out.  I got an unexpected message when I tried to drop the user:

Before the user can be dropped, any schema owned by that user must be dropped first or else you’ll get the above “database principal owns a schema, and cannot be dropped” message.  In my case, a matching schema had been created with the user which is why I created the schema for this example.  Schemas and their owners can be found with:

SELECT s.name AS 'schema_name'
,u.name AS 'user_name'
FROM sys.schemas s
INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name

There is our culprit.  Dropping this schema and then the user is simple enough:

Kingpin is gone and we can all sleep soundly…

Thanks for reading!