Many DBA's struggle with maintaining their security infrastructure while providing the ability for dev to access the delivery environment. A common approach is to setup a NRT (Near Real Time) environment on a warm server. There are several approaches to this. If you're looking just to push data SSIS or replication seems to fit the needs. The problem with these approaches is that it's not a true representation of delivery. Since you're only shipping data it often leaves out schema items such as stored sprocs and views that might have changed in the last release. In order to ensure development gets an actual representation of the DB the best method is to ship the entire database. This requires replicating all transactions both dml and ddl in the db.
This need leaves you with one of two choices, log shipping or mirroring with a snapshot. Either approach requires maintaining a read-only copy of the db, with occasional refreshes. For example in log shipping you might restore every hour in norecovery standby mode. Or in mirroring every hour you peal off another snapshot.
Although moving the database provides users with most of their needs, there is the issue of security. SQL Server has two levels of security the first is in the login where the user authenticates at the server level. When a user authenticates against the server there corresponding SID in the syslogins table is cached. It is this SID that gives the user access to the DB's on the server. If a user's login SID at the server level, is identical to a user SID in the database, the user has the permissions of that user inside the DB. If you create a user called "DEV", that user is assigned a random SID, on the server it was created on. When you give that user access to a database the login SID is added to the sysusers table in the database. It's the SID that's tracked not the user name, hence why you can have a different user name then login name. If you log ship or mirror to another server the database in which the user Dev was created, requires an identical SID on that server. This is because the user (DB) SID, resides inside the DB. Having a database with users not corresponding to Login SIDs or users with a different SIDs is referred to as orphaning a user.
There are a few ways to sync the user and login SID. The first is to execute the sp_change_users_login system stored procedure. This is well documented in Books Online, however the issue here is that it changes the SID inside the DB, and since log shipping or snapshots are read-only, you can't write, even to a system table. A different approach is to create the user on both servers with the same SID. For example if on your source server you create a user called "orphan", the server will generate a SID at the server and database level that match. To see this run the following commands.
CREATE LOGIN orphan WITH PASSWORD = 'Password123456';
go
use Adventureworks
CREATE USER orphan FOR LOGIN orphan;
select sid from sys.sysusers where name = 'orphan';
select sid from sys.syslogins where name = 'orphan';
Once created on the source server take the SID you queried for and create the same user on your destination server.
CREATE LOGIN orphan WITH PASSWORD = 'Password123456',
sid = 0x61BECB8A1CB0394FB5FC7393F6C0C3A2
Now any database moved from the source server to secondary server that contains this user can be accessed by this account. However this isn't the full fix quite yet. If the user's SID and password is the same on both servers, then anyone that knows the password can access both servers. The simple change is to have different passwords on each server. This is the nice part of this two phase authentication, you're not tied to a password but a sid. So on the second server run the following command.
CREATE LOGIN orphan WITH PASSWORD = 'NewPassword123456',
With this in place data can be logged shipped or mirror/snapshot without any security issues in place. One other note this is based on sql server authentication. Using windows authentication presents a different problem, in that the SID is the NT SID, and hence no matter what server you're on changing the password is not a possibility.
