Hello all, I have 2 servers- one development and one production - they both have the same database A and same SQL server login BB for this database. I wanted to refresh database in DEV by doing the restore a backup from Production. The restoration complete successfully . HOwever the SQL server login BB doesn't work in database A in DEV anymore, i have to drop user BB from database in DEV and give it permission again . Do i have to do it everytime I refresh my database in DEV from production or there're something i just need to do after i restore the database so i don't need to drop the user.?
One workaround is to use the sp_help_revlogin stored procedure to create the same users (including their SIDs) in your DEV environment that exist in your PROD environment. Syncing the SIDs will eliminate this orphaned user issue completely.
Keep in mind that sp_help_revlogin will also bring over their passwords in encrypted form. This means that the users passwords will be the same in DEV and PROD. This might not be a problem, but I prefer to have different passwords in use across a DEV/TEST/PROD landscape.
I can't remember where I found this on this site but here is a simple script that works great! I think Tara wrote it....
SET QUOTED_IDENTIFIER OFF GO
DECLARE @SQL varchar(100)
DECLARE curSQL CURSOR FOR select "exec sp_change_users_login 'AUTO_FIX','" + name + "'" from sysusers where issqluser = 1 and name NOT IN ('dbo', 'guest')
OPEN curSQL
FETCH curSQL into @SQL
WHILE @@FETCH_STATUS = 0 BEGIN EXEC (@SQL) FETCH curSQL into @SQL END
Restore database
Posted - 02/17/2006 : 11:56:00
