Sql-server – Running a stored proc with execute as

permissionsrolesql serversql-server-2016

I'm using SQL Server 2016

I have a basic stored procedure using execute as that I would like to run, but it comes up with this error when I try (names have been anonymised):

Msg 15404, Level 16, State 19, Procedure usp_executeastest, Line 0
[Batch Start Line 2]

Could not obtain information about Windows NT group/user
'MyCompany\A.Smith', error code 0x54b.

Here is my stored proc:

CREATE PROCEDURE [dbo].[usp_executeastest]
WITH EXECUTE AS 'dbo'
AS
BEGIN

SELECT * FROM [dbo].[TestTable1]

END

Now the database user 'dbo' is mapped to login 'MyCompany\A.Smith' so this explains why the error message mentions MyCompany\A.Smith.

I have tried running this proc by logging in as various users, including logging in as 'MyCompany\A.Smith' who is mapped to the dbo user, and the same error message comes up every time.

So why can't any user run this stored proc?

Best Answer

If you put dbo, it will look at the dbowner of the database, in your case (I guess) 'MyCompany\A.Smith'

Be sure the user you want to use in the execute as clause is part of sys.database_principals or sys.server_principals.

If not, you can fix this issue changing the owner of the database you use.

First, check the current owner :

select suser_sname(owner_sid) 
from sys.databases 
where name = 'MyDatabase'

If it's 'MyCompany\A.Smith' your procedure will not run.

So, change the owner, for instance :

sp_changedbowner 'sa'

And it will work.

Related Question