The server principal owns one or more event notification(s) and cannot be dropped.

Background:

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:

2016-11-14_215815.png

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:

2016-11-14_214004

Solution:

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

2016-11-14_220722.png

What are your thoughts on end users owning objects such as event notifications or mirroring endpoints? Does your organisation have any rules around this?

Advertisements

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s