Thursday, June 1, 2006

SQL Server: How to fix an orphaned user

I recently was sent a backup file to restore by a consultant. The file had logins I had already defined as users of the database. When I restored the backup, I was unable to sign in with a known user:

C:\>osql -S SERVER01 -d TestDB -U JSmith Password: Cannot open database requested in login 'TestDB'. Login fails.  

My login, JSmith, had become orphaned. This means that, although I had a login named JSmith, the internal representation of JSmith in my database did not match the internal representation in the backup.

There is a simple piece of code to fix this:

EXEC sp_change_users_login 'Auto_Fix', 'JSmith' 

If this works, you will get a message like this:

 The number of orphaned users fixed by updating users was 1.  

I did this and my login started to work again.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.