Possible to CREATE EVENT NOTIFICATION with a different owner?

Question

We are in the process of removing a previous dba login and he owns all the endpoints and event notification objects. Endpoints were easy to change; Event notification objects not so much.

I found this thread about changing the owner of an event notification object (you have to drop and recreate). I don't want to go through this process again if I can avoid it. I doubt it's possible, but outside of logging in as another user, can you create an event notification that runs as sa, etc.?

score 2 · Accepted Answer · edited Dec 07 '22 at 16:10

I thought that ALTER AUTHORIZATION might offer a way to transfer ownership, but no, according to the docs (emphasis mine):

Ownership of the following entities cannot be transferred: <many things> and event notifications.

So, script them, drop them, and re-create them under a more generic account that is not tied to an employee. As an aside, you can use PSExec to log in under a service account, like NT Authority\System, and use that account to create the notification.

This is similar to things like the ANSI_NULLS setting for tables (which I dealt with recently re: persisted computed columns).

score 1 · Answer 2 · answered Dec 07 '22 at 17:18

Wrapping CREATE EVENT NOTIFICATION inside EXECUTE AS LOGIN = N'sa'; ... REVERT; allows creation of a server event notification as if you logged in using the sa account, even if that account is disabled.

A very minimal example follows. First we'll check to see if the [sa] login is disabled:

/* Is the [sa] login disabled? */
SELECT 
      [sp].[name]
    , [sp].[sid]
    , [sp].[is_disabled]
FROM [sys].[server_principals] [sp]
WHERE [sp].[sid] = 0x01;

name	sid	is_disabled
sa	0x01	1

Create the event notification:

EXECUTE AS LOGIN = N'sa';
CREATE EVENT NOTIFICATION [notification_name]
    ON SERVER
    FOR <event list>
    TO SERVICE N'service_name', N'current database';
REVERT;

Then check ownership of the server event notification via this query:

SELECT 
    [owner] = sp.[name]
    , sen.*
FROM [sys].[server_event_notifications] sen
    INNER JOIN [sys].[server_principals] sp ON sen.[principal_id] = [sp].[principal_id];

owner	name	object_id	parent_class	parent_class_desc	parent_id	create_date	modify_date	service_name	broker_instance	creator_sid	principal_id
sa	notification_name	647673355	100	SERVER	0	2022-12-07 10:58:31.700	2022-12-07 10:58:31.700	service_name	073682F8-D45B-4C41-9FD9-523C90F662A2	NULL	1

As you can see in the results above, the owner is now sa.

Possible to CREATE EVENT NOTIFICATION with a different owner?

2 Answers2