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