Tuesday, April 13, 2010

How to Create SQL Users that Restore well.

In our Test and Development environments we are always restoring our production databases.  We use a couple SQL Server Users, which means that when we restore a database we have to always drop the database user and reassign the Server User.

Sometimes, we had difficulties in removing one or two users.  As a part of our application we started log shipping for reporting purposes, so reports wouldn't take down production.  When we did this I found this article on the issue with SQL Server User account.  It has a unique SID on each server, so we needed to recreate the user with the same SID for us to be able to assign SQL Server Users to our database.

How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server

Once I got this working for log shipping I realized that I could create this same user/SID on each of my Test/Development boxes and we wouldn't have to fix the users every time we did a restore from production.