Saturday, January 21, 2012

How to fix orphaned SQL Server users

How to Remove Orphaned Users From Database
"When you restore a SQL Server database on a different machine, you cannot access the database until you fix the permissions"

The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.
This used to be a pain to fix.
Now I am telling you the multiple solutions for fixing the orphaned user problem.

  • To find orphaned users:
EXEC sp_change_users_login 'Report'
  •  If user exist then remove one by one, and execute:
    EXEC sp_change_users_login 'update_one', 'orphan user name'  'orphan user name'
  • If you already have a login id and password for this user, fix it by:
EXEC sp_change_users_login 'Auto_Fix', 'user'
  • If you want to create a new login id and password for this user, fix it by:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
Now you will be able to access your database


0 comments:

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Code Imagine