I was doing some testing with event notifications and noticed that my AD login was created on the SQL Server (normally, my access is through an AD group and not an individual user account defined on the server). When I attempted to delete the login, I received the following error:
In my opinion, having objects created and owned by an end user isn’t great and should be avoided if possible. For example, if the DBA who originally created the object leaves the organisation, the above error is returned when removing the associated account. Instead, have the objects owned by a generic user that isn’t likely going to cause any issues with object ownership on the server.
Usually, a quick ALTER AUTHORIZATION does the trick (e.g. Mirroring endpoints) but unfortunately it doesn’t work on event notification objects as described in the “Remarks” section of the following article:
“Ownership of the following entities cannot be transferred: linked servers, statistics, constraints, rules, defaults, triggers, Service Broker queues, credentials, partition functions, partition schemes, database master keys, service master key, and event notifications.”
The following example will create an event notification for the EVENTLOG event type:
USE [mydb] GO CREATE EVENT NOTIFICATION [Event_EventLog] ON SERVER FOR EVENTLOG TO SERVICE 'ServiceBroker_Service', 'CURRENT DATABASE'; GO
Notice the principal_id belongs to the user who executed the example above:
In order to change this to ‘sa’ for example, simply drop the event notification and recreate with an additional step invoking the EXECUTE AS LOGIN syntax:
USE [mydb] GO EXECUTE AS LOGIN = 'sa'; GO CREATE EVENT NOTIFICATION [Event_EventLog] ON SERVER FOR EVENTLOG TO SERVICE 'ServiceBroker_Service', 'CURRENT DATABASE'; GO
What are your thoughts on end users owning objects such as event notifications or mirroring endpoints? Does your organisation have any rules around this?