Transfer Logins and Jobs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Transfer Logins and Jobs

Author

Topic

CanadaDBA

583 Posts

Posted - 2008-10-30 : 11:57:30

In order to build a DR box, the plan is to transfer database backups and restore in DR every night. This specific production server has databases which are added or removed. It also has jobs and user logins which are added or removed. So, I need to reflect these changes in DR box as well. Means We also need the jobs and logins to be transfered, too.

I have done the bakup transfer and restore.

Regarding login transfer, I have two ways to choose:
1. Grap production logins script and run in DR to reproduce the logins.
2. Use DTS package to transfer logins

I like the DTS way but not sure if it automatically drops all the logins and recreates those from Production. Is this DTS way recommended at all?

Thanks,

Canada DBA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-30 : 12:19:23

With DTS you can transfer login, but you have map Database users to Login to match correct SID.

CanadaDBA

583 Posts

Posted - 2008-10-30 : 14:47:20

Here is the plan: An automate DTS transfers the logins every night. Then database backups are being restored. And finally, A DTS will import the jobs.

Re Orphand logins, it seems I have to write a script to go through the logins and databases and repair them by something like:


USE <database>
GO
sp_change_users_login 'update_one', '<login_name>', '<login_name>'

1. Does anyboday have such a script ready to fix all orphand logins in all databases?

2. Does the Login Transfer DTS drops all the existing logins before it import from the other server?

3. Does the Job Transfer DTS replaces existing jobs with those on the source server?

I doubt the DTS packages drop existing logins or jobs. How can I make sure I have the same logins and jobs in my destination server?

Canada DBA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-31 : 10:36:47

1. Does anyboday have such a script ready to fix all orphand logins in all databases?

I use this one:

DECLARE @UserName nvarchar(255)
DECLARE Cursor_OrphanedUser cursor for
SELECT NAME FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x01) and suser_sname(sid) is NOT null ORDER BY name
OPEN Cursor_OrphanedUser
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' Synchronization of Logins in Progress'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName
END
CLOSE Cursor_OrphanedUser
DEALLOCATE Cursor_OrphanedUser
Go
2. Does the Login Transfer DTS drops all the existing logins before it import from the other server?

I am not sure about DTS but if you do through SSIS for Transfer Logins task you will get option to drop existing or rewrite
3. Does the Job Transfer DTS replaces existing jobs with those on the source server?

Yes it will If you choose to overwrite.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources