SQL map a login to an existing user

Question

I have recently upgraded my development environment from SQL 2000 to SQL 2008 R2. I've done a backup of our production db and restored it our new dev server.

I have created a login on the dev server which mirrors the login I use on the production server, but I can't get it mapped to the 'dbo' user in the database. When I edit the properties of the login in 'User Mapping", I replace the user with 'dbo', and I get the following error:

TITLE: Microsoft SQL Server Management Studio Create failed for User 'dbo'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) User, group, or role 'dbo' already exists in the current database. (Microsoft SQL Server, Error: 15023)

So how do I map a login to an existing user?

Answer 1

To reconcile the the user with the login, you can used the system stored procedure sp_change_users_login.

sp_change_users_login [ @Action = ] 'action' [ , [ @UserNamePattern = ] 'user' ]  [ , [ @LoginName = ] 'login' ]  [ , [ @Password = ] 'password' ]; 

For example:

EXEC sp_change_users_login 'Update_One','User123','User123'

If you have a lot of users who are out of sync, you can use a cursor to pull out all of the users and run this command for them. There isn't any adverse effect to running this against users that aren't out of sync, and it will fix all of the orphaned users.

DECLARE @sql NVARCHAR(MAX); DECLARE curSQL CURSOR FOR        SELECT   'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''        FROM     sysusers        WHERE    issqluser = 1                 AND name NOT IN ( 'guest', 'dbo', 'sys', 'INFORMATION_SCHEMA' ) OPEN curSQL FETCH curSQL INTO @sql WHILE @@FETCH_STATUS = 0       BEGIN            EXEC (            @sql            )            FETCH curSQL INTO @sql      END CLOSE curSQL DEALLOCATE curSQL 

This has to be run of the context of the database you need the users fixed in.

Answer 2

I think Nikola Markovinović's comment to this post needs to be added as an answer. Use the Alter user command:

USE {database};  ALTER USER {user} WITH login = {login} 

Where:

• {database}: The database containing the orphan user
• {user}: The orphan user name
• {login}: The login name. You can use the same login as used on the old server or map the user to a different login name

I found this answer at http://www.aip.im/2010/05/re-map-database-user-to-login-in-sql-server-after-restoring-or-attaching-database/#sthash.fbazv94Z.dpuf