Mirrored Database in Disconnected State - Have you Changed the Mirror Endpoint Owner Recently?

Change Mirroring Endpoint Owners and GRANT CONNECT Permissions on the Endpoint

Let's say you have an ex-employee who at some point created a database mirror on one of your SQL servers. That employee leaves the company so you need to remove them from the differnet environments they had rights to. When you go to remove the account you find out they are the owner of a mirroring endpoint. You use ALTER AUTHORIZATION to change the owner of the mirroring endpoint to the SA account. Simple enough, right?

Did you know, when you change the ownership of a mirroring endpoint SQL server drops any existing GRANT CONNECT permissions on that endpoint? The next time you restart your server all mirrored databases would be in a disconnected state and inaccessible unless you issue GRANT CONNECT permissions to the SQL service account for the mirroring endpoint.

Follow this guide to change an endpoint owner and grant permission on the endpoint.

-- Find out who owns the mirroring endpoint

SELECT
   me.name AS EndpointName    
  ,sp.name AS EndpointOwner -- ID of the principal that created and owns this endpoint
  ,me.type_desc AS EndpointDescription
  ,me.endpoint_id AS EndpointID
  ,me.state_desc AS EndpointState
  ,role_desc AS RoleDescription -- Can be none, partner, witness, or all
FROM sys.database_mirroring_endpoints me
INNER JOIN sys.server_principals sp ON me.principal_id = sp.principal_id

Let's assume the owner of the endpoint is a former employee of the company and we would like to remove their account from the server. Before we can remove their account we first need to change ownership of the endpoint they own. Before you do this, we need to look at permissions on the server endpoints.

-- Check permissions on server endpoints

SELECT
   e.type_desc AS EndpointType
  ,e.name AS EndpointName
  ,sp.[state_desc] AS PermissionState
  ,sp.[permission_name] AS PermissionName
  ,SUSER_NAME(sp.grantor_principal_id) AS GrantedBy
  ,SUSER_NAME(sp.grantee_principal_id) AS GrantedTo
FROM sys.server_permissions sp, sys.endpoints e
WHERE sp.major_id = e.endpoint_id
   AND sp.class_desc = 'ENDPOINT'

You should see a list of permissions on all of your endpoints on the server. We are looking for endpoints of the type DATABASE_MIRRORING, with the GRANT CONNECT permission assigned to the SQL Service Account. If you don't see this permission then you probably changed the owner of the mirroring endpoint without granting connect permissions back to the SQL service account. Keep reading.

-- Change endpoint owner to sa

ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa; -- Mirroring is the mirroring endpoint name

-- Let's check the owner again

SELECT
   me.name AS EndpointName    
  ,sp.name AS EndpointOwner
  ,me.type_desc AS EndpointDescription
  ,me.endpoint_id AS EndpointID
  ,me.state_desc AS EndpointState
  ,role_desc AS RoleDescription
FROM sys.database_mirroring_endpoints me
INNER JOIN sys.server_principals sp ON me.principal_id = sp.principal_id

-- Now let's check the server permissions on the endpoint

SELECT
   e.type_desc AS EndpointType
  ,e.name AS EndpointName
  ,sp.[state_desc] AS PermissionState
  ,sp.[permission_name] AS PermissionName
  ,SUSER_NAME(sp.grantor_principal_id) AS GrantedBy
  ,SUSER_NAME(sp.grantee_principal_id) AS GrantedTo
FROM sys.server_permissions sp, sys.endpoints e
WHERE sp.major_id = e.endpoint_id
   AND sp.class_desc = 'ENDPOINT'

Do you see the GRANT CONNECT permission to your service account? No? If you restarted your SQL server right now your mirrors would go in to a disconnected state and the mirrored databases would not be able to be accessed until you issued GRANT CONNECT on the mirroring endpoint.

-- GRANT CONNECT on Mirroring endpoint

GRANT CONNECT ON ENDPOINT::Mirroring TO [YourDomain\DomainAccount];

-- Now let's check the server permissions on the endpoint once again

SELECT
   e.type_desc AS EndpointType
  ,e.name AS EndpointName
  ,sp.[state_desc] AS PermissionState
  ,sp.[permission_name] AS PermissionName
  ,SUSER_NAME(sp.grantor_principal_id) AS GrantedBy
  ,SUSER_NAME(sp.grantee_principal_id) AS GrantedTo
FROM sys.server_permissions sp, sys.endpoints e
WHERE sp.major_id = e.endpoint_id
   AND sp.class_desc = 'ENDPOINT'

You should see the GRANT CONNECT permissions now. Follow these steps to ensure a disruption-free change to your Mirroring endpoint owner!

Here are some additional reference you can follow if you need to further troubleshoot your database mirror.

-- STOP and START a mirroring endpoint

ALTER ENDPOINT Mirroring STATE=STOPPED -- Mirroring is the name of the mirror endpoint

ALTER ENDPOINT Mirroring STATE=STARTED -- Mirroring is the name of the mirror endpoint

-- Resume mirroring after entering a suspended state

ALTER DATABASE <DatabaseName> SET PARTNER RESUME

-- Create a new mirroring endpoint

CREATE ENDPOINT Mirroring

STATE = STARTED

AS TCP (LISTENER_PORT = 5022) -- 5022 is the default port

FOR DATABASE_MIRRORING (ROLE=PARTNER);