Moving SQL Databases and Retaining Users
If you’ve ever tried to pack up and move a SQL Server database from one computer to another, you’ve probably noticed that the permissions and users won’t line up between the two machines. Even if you create accounts with the same usernames on the target machine, the users from one SQL Server are not recognized by the other. As a result, you receive error messages like, ‘Login failed for user USERNAME’.
Fortunately, there are at least two ways to solve this, one which is a bit of a shortcut and the other which is considered best practice.
METHOD 1: Drop the users from the database and re-create them
The fastest (shortcut) approach is to simply drop the users from the database on the new server, create replacement users on the server, add them to the database, and then apply the appropriate permissions to these replacement accounts. So long as the permissions for the database are relatively simple and number of users is small, this could be an easy solution. However, if you have a large number of users to transfer, or if you have complex permissions configured in the database, this might be a real hassle.
Another potential hang-up here is that sometimes you can’t just drop a user from a database because that user owns objects within the database. In this case, you need to reassign the ownership of those objects (which can be tables, views, etc.) to another user and schema. For instance, you may need to reassign them to the dbo (database owner) schema.
METHOD 2: Transfer the users
Another method sanctioned by Microsoft, is to transfer the users to the new server. You can use a SQL script and stored procedure to extract the user information and recreate the same users on the new server. This procedure is described in detail in a pair of Microsoft Knowledgebase articles (one for older versions of SQL Server and one for more recent versions). You can find the article describing how this is done for SQL Server 7 and SQL Server 2000 here . The article for SQL Server 2005 and SQL Server 2008 is here. These articles describe a methodology in which you can transfer the users, including the original Security Identifier (SID) to the new server. This alleviates the need to delete users, recreate them, and then reassign the permissions. This represents the best practice for this situation.
If you’re struggling to make this work, or have questions about the process, please contact us for some help. Once you have the hang of it, this is a very valuable methodology; particularly if you are continually moving databases (as often happens in test and development environments).
Read more posts by Sean Kuner