[Event Notifications] Changing security on the endpoints.

(Note: I still owe Justin some help on EN)


Recently had an issue trying to remove my login from a server (I've been transitioning from Dev & Ops to more Dev & Arch), and found I couldn't because of some of the permissions I had.  Namely: EN's (and thus Service Broker's) endpoint.

A former coworker running "SQLWatchdog" (aka my EN solution) came up with this code to fix the issue.  The second block of code fixes the endpoint for Always On.

And here's someone else who had the problem.  Code courtesy of both of them.


http://akawn.com/blog/2014/06/identify-and-change-a-sql-server-endpoint-owner/

USE master;

SELECT SUSER_NAME(principal_id) AS endpoint_owner

  ,NAME AS endpoint_name

FROM sys.service_broker_endpoints;

USE master;

ALTER AUTHORIZATION ON ENDPOINT::ENAudit_Endpoint TO sa;

USE master;

SELECT SUSER_NAME(principal_id) AS endpoint_owner

  ,NAME AS endpoint_name

FROM sys.service_broker_endpoints;

--------------------------------------------------------

USE master;

SELECT SUSER_NAME(principal_id) AS endpoint_owner

  ,NAME AS endpoint_name

FROM sys.database_mirroring_endpoints;

USE master;

ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO sa;

USE master;

SELECT SUSER_NAME(principal_id) AS endpoint_owner

  ,NAME AS endpoint_name

FROM sys.database_mirroring_endpoints;