How to sync logins(principal and mirror)

• suraj.jk

  SSC Journeyman

  Points: 82

  More actions

  February 2, 2017 at 1:27 pm

  #326549

  I have SQL server 2014.In that I configured mirroring for one database.so,we created some logins on principal server.
  How to sync. only these particular logins on mirror server. 

  Please give me advice.....

  Thanks,
  Suraj K

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  February 2, 2017 at 3:16 pm

  #1926498

  The easiest way is with the following script. Note this just creates the login. It does not do anything with any settings concerning that login. It doesn't do 'sa' either and only does specific types of logins.

  If you have already created them you can use the system sproc: sp_chanage_users_login

  SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) +
', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service