How to synchronize logins, etc. for database mirroring

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?

more ▼

asked Oct 12, 2009 at 09:26 AM in Default

graz gravatar image

graz ♦
525 2 4 5

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Oct 12, 2009 at 09:08 PM

mrdenny gravatar image

928 3

(comments are locked)
10|1200 characters needed characters left

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.


more ▼

answered Oct 13, 2009 at 12:53 PM

Jason Cumberland gravatar image

Jason Cumberland
507 2

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, 2009 at 10:08 AM graz ♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 13, 2009 at 12:50 PM

antxxxx gravatar image

11 1

I think a better solution is just to create them with the proper SID.
Oct 15, 2009 at 10:06 AM graz ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 12, 2009 at 09:26 AM

Seen: 3907 times

Last Updated: Nov 09, 2009 at 08:35 AM