Problems when creating logins for users who already exist in a SQL Server database

I came across this one again today and realised that I had never blogged about it previously.

I had a development manager contact me because she could not create a user (e.g. xyz) to access a database (e.g. userdb1) on a SQL Server 2005 server (e.g. server2) without getting “User, group, or role ‘xyz’ already exists in the current database. (Microsoft SQL Server, Error: 15023)”

This is quite common when dealing with multiple servers (development, test, stress, production , & business continuity). In this example, both the user database, userdb1, and the user, xyz, were created on a different server, server1, originally and xyz was given access to userdb1. Then the database, userdb1, is backed up, copied to server2 and restored there.

The login xyz does not exist on server2 yet and when one tries to recreate it, it finds a reference to xyz already in the restored userdb1 and cannot match the login to the user of the same name.

USE userdb1

GO

EXEC sp_change_users_login ‘report’

will report all users there for which no login information can be found in the master database.

You can then run this to link the master login back to the user in userdb1

USE userdb1

GO

EXEC sp_change_users_login ‘update_one’, ‘xyz’, ‘xyz’

Pinal Dave has a more comprehensive blog post than mine on this common problem.

Advertisements
Leave a comment

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: