Lesson Learned #242: The server principal "xyz" is not able to access the database "Yzxxx"

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request that our customer got the following error message: Msg 916, Level 14, State 4, Procedure dbo.StoreProcDemo, Line 0 [batch Start Line 0] - The server principal "xyz" is not able to access the database "Yzxxx" under the current security context.

 

 

 

Following I would like to share the main cause of this issue and a solution for this:

 

 

 

Our customer is running the following stored procedure:

 

 

 

CREATE or ALTER PROCEDURE dbo.StoreProcDemo

WITH EXECUTE AS owner

AS

SELECT 'jmjurado'

GO

 

 

 

At the moment of the execution of this stored procedure Exec dbo.StoreProcDemo our customer got the error message that we mentioned before.

 

 

 

All points to that the message is related about inherited permissions from the owner of the databases and I checked the owner of the database and I found that is empty.

 

 

 

Trying to find this problem I tried to use the following EXEC sp_changedbowner 'xyz' but sp_changedbowner is not supported and the only way that I found to assign a new owner was to run the following command: ALTER AUTHORIZATION ON database::Yzxxx TO [xyz]

 

 

 

After this execution and with a new owner of the database the stored procedure was executed correctly.

 

 

 

Enjoy!

 

Continue reading...