Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm restoring a backup of a production database to a new server. The new server also hosts the application which makes use of the database. As such I'd like to use the same user/pass combination to connect to the database. The user is also included in the database backup so I thought that should work. However the user doesn't exist as a (database) user on the new server. So I created a new server user and tried to map this credential to the newly restored database. However this fails because the user already exists in the database.

Is it possible (and if so please tell me how) to move/copy a user from a database (server independent) to the list of (database) server logins?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

In SQL-Server 2008 the database users for a restored database and server logins may be out of sync because the internals IDs do not match. To fix this run:

EXEC sp_change_users_login @Action=''Update_One'', @UserNamePattern='databaseusername', @LoginName = 'logonname';

against your restored database and for each user/login.

In SQL-Server 2012 there is a new option to use database users independent of server logins.

share|improve this answer
    
Great!, I've been struggling with this for ages and this works perfectly. –  Ropstah May 30 '12 at 16:17

I know that I'm late on this, but it might help someone else.

Logins are at the server level. Users are at the database level. There is an ID value (called a SID) that joins the two together. When you create a SQL login on a new server, SQL pick a new SID value. The nature of the SID-picking algorithm is that you won't get the same value on two different servers. When you restore the database from the old server, the SID value from the database doesn't match the SID value on the server.

At that point, you want to use sp_change_users_login.

But, BEFORE you get to that point, you can save yourself a lot of aggravation, particularly if you have a log of logins to migrate from an old server to a new server. Look at sp_help_revlogin. This is a proc that is supported by Microsoft. The proc and documentation is located here and here. (There are different versions of the procedure for different versions of SQL Server. There is also a helper function called 'sp_hexadecimal'.) Basically, you need to create those procedures on your old server and then run them. The KB articles have details.

The big win of sp_help_revlogin is that it produces user creation DDL that preserves the ID value and the password for SQL logins. This way, you can just run sp_help_revlogin, look through the generated DDL to find the users you need, run that code on the new server and then just restore the database. At that point, the SID values should match up and you won't need sp_change_users_login.

The other way around this is to use integrated (aka "domain" or "windows") security. When using AD logins, the SID value is pulled from the domain when you create the login. That SID value is then placed in the database when you create the database user for that login. Since there is only one source for SID values, no mismatch can occur. Of course, using integrated security is not always possible.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.