Execute As

Last Post 23 Apr 2007 12:39 AM by SQLUSA. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sebjag
New Member
New Member

--
17 Apr 2007 08:55 PM
Msg 15406, Level 16, State 1, Procedure <Proc>, Line 13
Cannot execute as the server principal because the principal "User" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I have a trigger on a table that used to be created with the WITH EXECUTE AS <USER> clause on SQL Server 2005. When the trigger was fired I would get an error message stating that the user did not have execute permissions on msdb sp_start_job in schema dbo. I fixed this error by placing the EXECUTE AS LOGIN='<Login>' clause in the body of the trigger. The particular login/user is part of the server role sysadmin.

Now what happens is that when another user log in and fires the trigger by doing an updte to that particular table that the trigger is created on I get the following error:

Msg 15406, Level 16, State 1, Procedure <Proc>, Line 13
Cannot execute as the server principal because the principal "User" does not exist, this type of principal cannot be impersonated, or you do not have permission.

The user that I am logging in as is different to the user that is int he EXECUTE AS statement, it is a user with less previlidges on the server. I have tried running a number of combinations of the GRANT IMPERSONATE and GRANT CONTROL statement with both users with no luck.

I had the user that I was logging in as placed in the db_owner role and the db_scurityadmin database role that the table and trigger where in.

What am I doing wrong, the only way around this error I found weas to place the user Im logging in as into the sysadmin server role, which is something I do not want to do as it is a developer login account and I do not want them to have sysadmin rights.

Please help.. Thanks.
SQLUSA
New Member
New Member

--
23 Apr 2007 12:39 AM
You may have some permissioning issue.

Can you list the trigger?

Can you list the setup of the 2 logins involved?

Kalman Toth, Database Architect
SQL Server Training - http://www.sqlusa.com
sebjag
New Member
New Member

--
23 Apr 2007 02:18 PM

AS
BEGIN
EXECUTE AS LOGIN='MARS_App';

SET NOCOUNT ON;
DECLARE @counter1 int,
@counter2 int
select @counter1=count(*) from MD.ETLBatchAudit where Stage='ODS' AND Activity='PENDING_USER_WARNING'
if @counter1>0
BEGIN
EXEC msdb.dbo.sp_start_job N'ETL Starter - ST'
END
select @counter2=count(*) from MD.ETLBatchAudit where Stage='ODS' AND Activity='LOADED'
if @counter2>0
BEGIN
EXEC msdb.dbo.sp_start_job N'ETL Finisher - ST'
END
END


The 2 logins invlolved are 'Mars_etl' which is just a standard user in the database and 'MARS_App' which is a sysadm.

I managed to resolve this problem by granting impersonate permission to the login MARS_etl at the server level, but im not sure if this is the correct way to do it.
You are not authorized to post a reply.

Acceptable Use Policy