Fix Orphaned SQL Server Logins

Monday, February 16, 2004When restoring a database, if the original SQL user accounts don't exist in the new SQL Server system, the database users will not be linked to the SQL Server users. Creating a new user with the same name won't work, because granting access to the restored database will generate a "user already exists" error.

To fix this,

1. Create the user as it should be in SQL Server
2. In Query Analyzer, run sp_change_users_login 'update_one', '<database user>', '<sql user>' in your restored database.
   
3. That's it!
   

1 Comment

• vic: Thank you... Just what I needed. Vic (commented on 4/6/2004 11:13:20 AM)

Comments are closed for this article.