Thursday, August 16, 2007

SQL Server 2005 Error: User or Role already exists in the current database

Just for me to remember. I'm currently doing migration of our applications and databases from the East Coast going to California. This is something that came up when I migrated one of my databases. I certainly would like to remember...

This is what happens when you restore the database to a new server, you expect that accounts could be configured to access the database via the "User Mapping". But instead there is this error message: "Error 15023: User or role already exists in the current database." Apparently, this is just a case of SID mismatch. Here's the solution:

SQL Server has a built in stored procedure to handle this issue:

sp_change_users_login

Use the parameter 'Update_One' to update one SID:

USE YourDatabase
GO

EXEC sp_change_users_login Update_One, 'yourLogin', 'yourLogin'
GO

Go to this post for further reading.