Managing Orphaned database users in SQL Server

It one of the most common issue you will face when you will move/copy your database from one server to another server. You will have all finely setup SQL Server Logins and their related database users but when you copy or move your database those Server Logins will not get transferred automatically. Result is what we call Orphan Users, these database users are there in database but there is no login associated with it. In other words they are just useless and will create various issues related to authorization if an application or users are using it.

There are many cases when you may need to manually map SQL Server Logins with database users. For example it is possible that you have different logins created in SQL Server and you wanted to use these new logins with existing users Or you might even have same logins but since they are on different machines it is very likely that they will have different SIDs (Security Identifiers) Or simply some users has left an organization and you want to assign new user existing login (which I think is more easy by use of ROLES rather than directly assigning a user) Or wanted to have different user name then server login.

For all of these condition there is a system stored proc called “sp_change_users_login“ which can be used to manually assign logins to database users. I have created a simple demo to display the concept and process of mapping existing users to new logins.

-- Step 1. Create Login and give that login access to some database which will create a related user in database
USE [master]
GO
CREATE LOGIN [UserFoo] WITH PASSWORD=N'foouser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDB]
GO
CREATE USER [UserFoo] FOR LOGIN [UserFoo]
GO
USE [TestDB]
GO
EXEC sp_addrolemember N'db_owner', N'UserFoo'
GO

-- Step 2. Drop Login
Drop login [UserFoo]
GO

-- Step 3. Create another Login but don't map that login with any database user
USE [master]
GO
CREATE LOGIN [UserBar] WITH PASSWORD=N'baruser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Step 4. Check Permissions for user 'UserBar'
USE [TestDB]
EXECUTE AS LOGIN = 'UserBar';
SELECT * FROM fn_my_permissions(NULL, 'Database') 
REVERT;
GO

-- Step 5. Check for Orphan users in database
USE [TestDB]
EXEC sp_change_users_login 'Report'

-- Step 6. Map user. Note the parameter in SP, "update_one" just to update only one record manually
USE [TestDB]
EXEC sp_change_users_login 'Update_One', 'UserFoo', 'UserBar';

-- Step 7. Check Permissions for user 'UserBar'
USE [TestDB]
EXECUTE AS LOGIN = 'UserBar';
SELECT * FROM fn_my_permissions(NULL, 'Database') 
REVERT;
GO

 

But say you want to map login with database user if it is already present or just create new user and map it with given login. In that case you have to use “Auto_Fix” in place of “Update_One” , which basically tries to map an existing user to a login of the same name, or to create the SQL Server login with password if the login does not exist. A sample code from MSDN is shown below,

-- From MSDN
USE AdventureWorks2008R2;
GO
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO

I personally like to use “Update_One” because it makes sure that I know what I am doing .

That’s it for now…

It’s Just A Thought …