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:
- 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.
- 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.