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. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: