Mirroring Endpoint Ownership

Creating a mirroring endpoint in SQL Server can sometimes do goofy things with permissions.

I’m facing an issue right now where two bad things are happening when one of our DBAs creates an endpoint:

  1. The creator’s account becomes the owner of the endpoint, which is not un-common in SQL Server. However, like other ownerships, this will prevent you from dropping the owning login.
  2. The creator’s account is added as an individual login to the instance. Even if that account already has access through a domain group, it still gets added as an individual (bug?). We’ve got to jump through hoops to remove the login and it can’t be removed until the endpoint’s ownership is cleared up.

We can fix it with a one-liner:

ALTER AUTHORIZATION
ON ENDPOINT::[MirrorMirrorOnTheWall]
TO [DOMAIN\ACCOUNT]

Set the owner to an account that’s going to be around for a while (NOT A PERSON). If the account you chose doesn’t have sysadmin you may need to also grant connect privileges to the endpoint:

GRANT CONNECT
ON ENDPOINT::[MirrorMirrorOnTheWall]
TO [DOMAIN\ACCOUNT]

With problem number one solved we are no longer restricted on dropping the creator’s login (provided the login doesn’t own anything else). We can drop the login but there’s a way to avoid this whole mess all together.

Using EXECUTE AS LOGIN we can create the endpoint as the original login:

EXECUTE AS LOGIN = 'DOMAIN\ACCOUNT'
    CREATE ENDPOINT MirrorMirrorOnTheWallFixed
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ENCRYPTION=SUPPORTED ALGORITHM AES, ROLE=ALL)
REVERT

Creating the owning login and then running the above script will keep things tidy. The endpoint gets created with the owner you determine and no individual logins get added to the instance.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s