July 16, 2009

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

0 Responses to "Detecting Orphaned Users in SQL Server"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions