I got error message below when i try to drop a login named ‘test_login’
Msg 15186, Level 16, State 1, Line 1
The server principal is set as the execution context of a trigger or event notification and cannot be dropped.
It means that there are execute as username in a stored procedure or function or triggers, and that is why it will not allow me to drop the user.
EXECUTE permission - a check is made to see if this permission is granted. If yes the user is allowed to run the stored procedure
EXECUTE AS - when the stored procedure is run, it will run under the context of the user specified in the clause rather than the context of the user who has run it.
Use the query below find the execute as object
|select object_name(object_id) |
where execute_as_principal_id = user_id('test_login')