SQL Server Error Msg 15136 – The database principal is set as the execution context of one or more procedures cannot be dropped

In this blog post, let’s learn about the error message “15136 – The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

15136 – The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

Reason for the Error

SQL Server Error Msg 15136 “The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped” occurs when you try to drop a database user or login that has been set as the execution context of one or more stored procedures, functions, or event notifications.

When a database user or login is set as the execution context of a stored procedure, function, or event notification, it means that the code within that object will run under the security context of the specified user or login. Therefore, if you try to drop the user or login while it is still being used as the execution context, you will receive the SQL Server Error Msg 15136.

Here’s an example of how this error can occur:

-- Create a stored procedure with the execution context set to a database user
CREATE PROCEDURE [dbo].[MyProcedure]
WITH EXECUTE AS 'testuser'
AS
BEGIN
    PRINT 'TEST'
END
GO

-- Try to drop the database user
DROP USER testuser;

When you execute the DROP USER command in this example, you will receive the SQL Server Error Msg 15136 because the MyUser database user is still set as the execution context of the MyProcedure stored procedure.

Msg 15136, Level 16, State 1, Line 11
The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped.

Solution

To resolve this error, you must first remove the user or login from the execution context of all stored procedures, functions, and event notifications that use it. You can do this by altering the objects to remove the EXECUTE AS clause or by changing the execution context to a different user or login.

Here’s an example of how you can modify the stored procedure to remove the EXECUTE AS clause:

ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
   PRINT 'TEST'
END
GO

-- Try to drop the database user
DROP USER testuser;

In this example, you first modify the MyProcedure stored procedure to remove the EXECUTE AS clause, which effectively removes the TestUser database user as the execution context. Then, you can execute the DROP USER command again without encountering the SQL Server Error Msg 15136.

If you have multiple stored procedures, functions, or event notifications that use the same user or login as the execution context, you will need to modify each of them to remove the EXECUTE AS clause or change the execution context to a different user or login.

Another option is to transfer ownership of the objects that use the database user or login as the execution context to a different user or login. Once you have transferred ownership, you can drop the original user or login without encountering the SQL Server Error Msg 15136.