Error 15023: User or role ” already exists in the current database.

As the title states “Error 15023: User or role ‘<name>’ already exists in the current database.” This usually happens after you have restored a database from a backup from one server to another.

I used to use sp_change_users_login. According to Microsoft on this link about its usage, it will be removed in the future versions of SQL Server. http://tinyurl.com/y9ftnpx. It does not say when in the future. I have used it many times on SQL Server 2005 and recently on SS 2008. So for now it still works.

Microsoft states you should use ALTER_USER instead. http://tinyurl.com/yg8g4vq

Both of these stored procedures basically do the same thing. Map existing database users to SQL server logins.

Since we are advised “not” to use sp_change_users_login I am not going to describe it further.

ALTER_USER-Renames a database user or changes its default schema.

Arguments:

userName: name of the user inside the database.

LOGIN: loginName

Remaps a user to another login by changing the Security Identifier (SID)

NAME: newUserName

The new name for the user, this name cannot already exist in the database.

DEFAULT_SCHEM: schemaName

This indicates the first schema that will be searched by the server when trying to resolve the names of the objects for the user.

Leaving the default_schema undefined will assign the dbo schema b default.

USE Database_Name;

ALTER USER User1 WITH NAME = User2;

GO

Hope this helps. Be sure to follow the link above to get more details.

Advertisements

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