Sync’ing sql server authentication logins between mirrored or logshipped databases in SQLServer

To Sync SQLServer authentication logins between mirrored instances:

First create the login on the source database.

Execute the below code in ssms on source server master database

select name,sid from syslogins 

Copy the name and sid and use the same sid when you are creating the login in the mirrored instance using the below code

create login  with password = '*******',sid = paste the sid here with no quotes,default_database = yourdatabasename, default_language = English

The default_database = yourdatabasename is not necessary as sqlserver by default will choose master database

Once this is done you can assign permissions to the login to a specific database and the log shipping or the mirroring takes care of the user permissions in the mirrored or logshipped database.

Hope this helps.

 

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s