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
Related Q&A from Greg Robidoux
Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings. Continue Reading
Restore backups in SQL Server 2000 and SQL Server 2005 with this command. Continue Reading
Back up tables from a SQL Server database to a file with these commands. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.