Changing SQL Database Owner for Lync 2013

When you install SQL back end the mirroring endpoint is set to the person who ran the installer (so in our case Domain\XXXXRichXXXX)

If you use the following SQL:
SELECT [PrincipalName] = sp.name, [PrincipalId] = sp.principal_id, me.*
FROM sys.database_mirroring_endpoints me with(nolock)
inner join sys.server_principals sp with(nolock)
on me.principal_id = sp.principal_id

SELECT EPS.name, SPS.STATE,
CONVERT(nvarchar(38),
SUSER_NAME(SPS.grantor_principal_id))AS [GRANTED BY],
SPS.TYPE AS PERMISSION,
CONVERT(nvarchar(46),SUSER_NAME(SPS.grantee_principal_id))AS [GRANTED TO]
FROM sys.server_permissions SPS , sys.endpoints EPS
WHERE SPS.major_id = EPS.endpoint_id

ORDER BY Permission,[GRANTED BY], [GRANTED TO]


You will get back the current endpoint owner (PrincipalName), what other endpoints can interact with the mirror (GRANTED TO) and who granted those rights (GRANTED BY)
This example is from XXX-SQL01 Principal, its partners are XXX-SQL02 mirror and XXX-SQL03 Witness:


As the Domain\XXXXRichXXXXX account is being removed we created a new Service Account called “DOMAIN\svc-lync-sqlmirror” (standard windows user).

Then on each endpoint in the SQL mirror (Principal, Mirror, Witness) ran the following (changing the server names as appropriate in the final SQL block):

USE [master]
GO
CREATE LOGIN [DOMAIN\svc-lync-sqlmirror] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\svc-lync-sqlmirror]
GO

ALTER AUTHORIZATION ON ENDPOINT::mirroring_endpoint TO [DOMAIN\svc-lync-sqlmirror]

GRANT CONNECT ON ENDPOINT::mirroring_endpoint TO [DOMAIN\XXX-SQL02$]

GRANT CONNECT ON ENDPOINT::mirroring_endpoint TO [DOMAIN\XXX-SQL03$]


  1. The first block adds the Service Account in as a SysAdmin
  2. Next block changes the owner to be the Service Account
  3. Last block grants permissions back to the other two SQL servers to be part of the mirror again (running the second block wipes this out - http://blogs.perficient.com/microsoft/2014/03/changing-the-sql-mirror-endpoint-owner-breaks-mirror-in-lync-2013/)


After running the above the owner has now changed:


Once all three mirror partners changed rebooted the servers in the following order:

  1. Witness  -  (XXX-SQL03)
  2. Mirror  -  (XXX-SQL02)
  3. Principal  -  (XXX-SQL01)
(note that your DB's will be on the Mirror at the end of this procedure so use Invoke-CsDatabaseFailover to fail back - https://technet.microsoft.com/en-us/library/jj204991(v=ocs.15).aspx or reboot the mirror again!)

Ensure you wait between each reboot to allow the mirror state to become stable again (all green ticks):


(to run this, Open SQL Server Management Studio > Right click a mirrored database > Tasks > Launch database Mirroring Monitor

Edited 12/07/2015 to note that the db's will be on the mirror server after performing this procedure

No comments:

Post a comment