Cannot execute as the database principal

Question

Cannot execute as the database principal

nsmith 8448

Old Hand

Points: 333
More actions
November 14, 2013 at 10:39 pm

#285178

I have been handed a task. I don't have a lot of experiance in SQL. I know this probably isn't the best approach. But, it is what I have been asked to make work (I can't sign the code or use asymetric keys). When I execute a stored procedure I get the following error. I think it is related to double impersonation with local SQL accounts.
Msg 15517 - Cannot execute as the database principal because the principal "user1" does not exist, this type of principal cannot be impersonated, or you do not have permission.
We have two diffrent instaces of MSSQL. Each instance has a database. In Instance 1 we have Database A. In Instance 2 we have Database B. We have a linked server in Instance 1 that connects to Database B. In Database A we have a stored procedure that retrieves data using the linked server from Database B. This stored procedure uses Execute As User with the impersonated account User1 and works when I run it from the console. However, when it is run in a script or from the console it fails. The stored procedure is being called with the Execute As User with the account user5. How can I get the double impersonation to work? For example:
We are calling the stored procedure this:
use 'Database A'
go
execute as user = 'user5'
go
schema-1.StoredProcedure_GetData
The stored procedure is executing a select statement with the Execute As User = 'user1'
Script 1
EXECUTE AS USER = 'user5'
(run against DB-A)
(Runs Stored Proc-1 on DB-A)
(Fails Here??)
|
|
MSSQL Instance-1
|
|
MSQL DB-A
|
|
|
Stored Proc-1
EXECUTE AS USER = 'user1'
(Gets data from DB-B)
(Using Linked Server)
(Execute AS USER seems to work)
||
||
MSQL Linked SVR 1
||
||
MSSQL Instance-2
|
|
|
MSQL DB-B

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

nsmith 8448 Old Hand Points: 333 More actions · Answer 1

I was able to get it working after going through the EXECUTE AS (Transact-SQL) article with fresh eyes. I didn't set the impersonate permission on the user account. To set it I used the following

USE DB-A;

GRANT IMPERSONATE ON USER::user1 TO user5;

GO

My issue is resolved. Thank you.