|
We're just starting to use database mirroring at a client. Obviously we need to copy over logins, role memberships, jobs, etc. to the target machine on a regular basis. I looked at the SSIS task to do this and wasn't very impressed. How are people doing this?
(comments are locked)
|
|
Either with an SSIS task, or setup a policy so that when an admin creates a login on one instance, that same login is created on the mirror instance as well. You could also look into writing an T/SQL script which collects all the logins and then creates them on the remote system nightly by hacking up sp_help_revlogin and using that.
(comments are locked)
|
|
I use a script and linked server to do this, running from a daily Agent job. This synchs up the password for SQL accounts and keeps the SID the same, similar to sp_help_revlogin. post: http://qcumberland.com/index.php/archives/2009/07/20/synching-logins-between-2-instances/ That's pretty similar to how I'm doing it now. I'll look at that script in more detail and see how it compares. Nice picture! :)
Oct 15 '09 at 10:08 AM
graz ♦
(comments are locked)
|
|
You will also need to ensure that the database users are mapped correctly to the logins on the server. Using this script when the mirror and principal swap roles should do it declare @users table ( username sysname, usersid varbinary(85) ) declare @username sysname insert into @users exec sp_change_users_login 'report' declare user_cursor cursor for select username from @users open user_cursor fetch next from user_cursor into @username while (@@fetch_status = 0) begin exec sp_change_users_login 'auto_fix', @username fetch next from user_cursor into @username end close user_cursor deallocate user_cursor I think a better solution is just to create them with the proper SID.
Oct 15 '09 at 10:06 AM
graz ♦
(comments are locked)
|
