Detecting Orphaned Users in SQL Server

As given in the BOL “A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance”

The most common causes of orphaned users are

- when SQL Server database is restored and attached on a different machine

- User is mapped to a SID that does not exist in the new machine

- When SQL Server login is undefined for a database user

- SQL Server Login is dropped

Fix Orphaned Users in SQL Server Database

Here’s how you can detect Orphaned users and fix them in a SQL Server Database

USE DatabaseName
GO
sp_change_users_login 'Report';
GO

Once orphaned users are detected, you can do three things:

- Map user to a SQL Server login using sp_change_users_login

--Create the new login
CREATE LOGIN SomeUserNm WITH PASSWORD = '222IJHUYd';
GO
--Map database orphan user OrphanUserNm to login SomeUserNm
USE DatabaseName;
GO
EXEC sp_change_users_login 'Update_One', 'OrphanUserNm', 'SomeUserNm';
GO

- Automatically mapping a user to a login

USE DatabaseName;
GO
EXEC sp_change_users_login 'Auto_Fix', 'OrphanUserNm', NULL, 'A5o23y@j99422a1;
GO

- Add or change a password for a Microsoft SQL Server login using sp_password

ALTER LOGIN 'OrphanUserNm WITH PASSWORD = '222a$IJHUYd';
GO

References:

http://msdn.microsoft.com/en-us/library/ms175475(SQL.90).aspx

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook