Export SQL 2008 Logins to Disaster Recovery site ?

Question

Post reply

Export SQL 2008 Logins to Disaster Recovery site ?

Express12

SSCoach

Points: 18514
More actions
November 5, 2009 at 5:23 am

#217148

Wanted to get some ideas as to how folks are EXPORTing their SQL Logins (on SQL 2008) from their active site to their D/R site on a daily basis.
We are using SQL 2008's DB Mirroring to actively populate the application DB's at the D/R site. Unfortunately, the SQL Logins are located in the master..syslogins system table which is not being Mirror'd!
Any feedback is greatly appreciated.. thx
BT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Express12 SSCoach Points: 18514 More actions · Answer 1

I've built an SSIS 2008 package incorporating the 'Transfer Logins Task'

BT

ramuvanparti SSCrazy Points: 2368 More actions · Answer 2

restoring master and msdb will give you all user login and job linked server etc which where present on old server

Regards

Ramu

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

This is what I use to gen out a list and then run it in the new SQL Server. However, it only works in SQL 2000. I have not seen a script for SQL 2005 yet.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Won't this(http://support.microsoft.com/kb/246133) link work for you?

MJ

philcart SSC-Forever Points: 47794 More actions · Answer 5

Try this one, http://support.microsoft.com/kb/918992

Using this procedure, you can use OSQL to output all the logins to a single file. If you want individual files per login, just loop through the logins using a cursor or while loop.

EG:

declare @server varchar(30)

declare @instance varchar(30)

declare @cmdText varchar(2000)

declare @sqlText varchar(2000)

declare @outputFile varchar(2000)

declare @login_name sysname

declare @outputDir varchar(500)

set @server = cast(serverproperty('ServerName') as varchar(30))

set @instance = cast(serverproperty('InstanceName') as varchar(30))

set @outputDir = 'my directory path'

declare curLgns cursor fast_forward for

SELECT p.name

FROM sys.server_principals p

WHERE p.type IN ( 'S', 'G', 'U' )

AND p.name <> 'sa'

AND not (p.name like '%##%')

ORDER BY p.name

open curLgns

fetch next from curLgns into @login_name

while @@FETCH_STATUS = 0

begin

set @sqlText = 'exec DBA..sp_help_revlogin @login_name = ''' + @login_name + ''''

set @outputFile = @outputDir + @server + isnull('_' + @instance, '') + '_login_' + replace(@login_name, '\', '~') + '.sql'

set @cmdText = 'osql -S. -E -Q"' + @sqlText + '" -o "' + @outputFile + '" -w255'

exec master..xp_cmdshell @cmdText, no_output

fetch next from curLgns into @login_name

end

close curLgns

deallocate curLgns

--------------------
Colt 45 - the original point and click interface