Earlier this week I ran into an issue while executing a DROP LOGIN statement.
USE [master]; GO DROP LOGIN [Domain\User]; GO
This would result in the following error message.
Msg 15141, Level 16, State 1, Line 2
The server principal owns one or more event notification(s) and cannot be dropped.
In order for me to drop the login, I also needed to drop the event notification(s) associated to this login.
To find the name of the event notification, login name and dynamically create the TSQL code to drop the event notification you can execute the following query. Make sure to replace the p.name value (Domain\User) with your login name.
SELECT s.name AS 'EventNotificationName' , p.name AS 'PrincipalName' , 'DROP EVENT NOTIFICATION '+s.name+' ON SERVER;' AS 'DropEventNotificationQuery' FROM sys.server_event_notifications s LEFT JOIN sys.server_principals p ON s.principal_id = p.principal_id WHERE p.name = 'Domain\User';
Once you drop the event notification(s), you should be able to drop the login successfully.