October 25, 2001 at 12:28 pm
I did a restore of databases to a disaster recovery box. Everything came over fine except the security because it was stored in the master of the production box. The disaster recover box has a master that I cant mess with so basically my question is how do I script out the logins from the master on the production box.
October 26, 2001 at 10:48 am
also need to match up the logins with the users once you are finished.
from the db, run "sp_change_users_login 'auto_fix'
Steve Jones
October 28, 2001 at 8:42 pm
NOTE: If you are using Integrated Security then the windows logins are mapped in the Windows Registry on the local SQL server.
In this case you definitely have to prepare and run a script of the original logins.
Try restoring a copy of the master database to say zTmpMaster, then run the following to generate your login script.
select ‘EXEC sp_grantlogin N’’’ + L.name + ‘‘‘‘ + char(10) + ‘go’ +char(10) +
‘EXEC sp_defaultdb N’’’ + L.name + ‘‘‘, N’’’ + D.name + ‘‘‘‘ + char(10) + ‘go’ + char(10) +
‘EXEC sp_defaultlanguage N’’’ + L.name + ‘‘‘, N’’us_english’’’ + char(10) + ‘go’
from zTmpMaster.dbo.sysxlogins L, zTmpMaster.dbo.sysdatabases D
where L.dbid = D.dbid AND L.name <> ‘sa’ and L.name <> ‘BUILTIN\Administrators’
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply