Problem solve Get help with specific problems with your technologies, process and projects.

Mismatches after moving a SQL database to a new server

We are in the process of moving the SQL database to a new server. I was trying to move the database by detaching it from one server and then copying the files to the new server and attaching it over there.

The database attaches without any problem. However, when I try to create the users and give them permissions to tables and stored procedures on the new server, it gives an error. Sysuser table of the attached database has user information from the old database. How can I clean up the old user info from the newly attached DB?

SQL Server stores LOGIN information in the MASTER database and USER information in the user databases. Because of this, when you restore a database from a different server or if you attach a database from a different server, you will get mismatches unless the servers were set up and administered the same way.

If you are moving a database to a brand new server where no login information yet exists, you can script out login information and apply it to the new server. This can be done using DTS or it can be done by writing T-SQL code to script out the data. This MSDN article goes into more detail on how this can be accomplished.

Also refer to sp_change_users_login in SQL Server Books Online. This stored procedure gives you information on mismatches as well as allows you to fix the mismatched data.

To identify and clean up orphaned users, you can write T-SQL code that will identify the users that do not have a corresponding login and then use the sp_revokedbaccess stored procedure to drop them. Refer to this article for a completed solution.

Dig Deeper on SQL Server Backup and Recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.