Friday, May 28, 2010

SQL Server: "Error '15023' User or role already exists in the current database" when you perform restore database from backup

When you are restoring a Microsoft SQL Server database (.bak) from backup, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to allow necessary user permissions to the new database. This is puzzling enough since a database backup should bring us back to the original state. This is caused by Security Identification numbers (SID) that are mismatched or 'orphaned' in the sysusers table.

To resolve this problem, we need to make use of the SQL Server stored procedure sp_change_users_login.
--Run it with a single parameter 'Report' to get a listing of abandoned user names and corresponding SIDs
exec sp_change_users_login Report

--'Update_One' as 1st parameter will reconnect a single login
exec sp_change_users_login Update_One, 'MyLogin', 'MyLogin'

No comments:

Post a Comment

Do provide your constructive comment. I appreciate that.