"Execute As" impersonation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

"Execute As" impersonation

Author

Topic

juancabrera
Starting Member

30 Posts

Posted - 2007-11-28 : 14:48:02

I have a stored procedure that looks like this:

Create Procedure DatabaseJobStart @JobName SysName
With Execute As 'XYZ-Domain\Administrator'
As
Begin
Set NoCount On

Execute Msdb.dbo.sp_start_job @JobName

Set NoCount Off
End
Go

If I were to run this stored procedure on Query Analyzer like this:

Declare @JobName SysName

Select @JobName = 'Billing: Projections'

execute DatabaseJobStart @JobName

I get the following error:

Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_is_starting', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure xp_sqlagent_notify, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
Job 'XYZ: Projections' started successfully.

The Login XYZ-Domain\Administrator is mapped to msdb and has db membership to all the sqlagent roles. Why am I getting this error? Impersonation is working, and the login has the right roles, I just dont' get it. Anyone?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 15:14:50

test permissions with this and post the results...

SETUSER 'XYZ-Domain\Administrator'
GO
EXEC Msdb.dbo.sp_start_job 'Billing: Projections'
GO

juancabrera
Starting Member

30 Posts

Posted - 2007-11-28 : 15:19:15

I runs just fine if I run it like that. That's why I am still scratching my head.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 15:41:07

did you run that from the database context the proc is in?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 15:49:17

if chaining is off, also check the database owner is the same for msdb and the database with the proc...

select name, suser_sname(owner_sid) from master.sys.databases

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-28 : 16:20:49

Is the DatabaseJobStart stored procedure in the msdb database, or in another database?

CODO ERGO SUM

juancabrera
Starting Member

30 Posts

Posted - 2007-11-28 : 16:51:28

To Anonymous: Q1 - Yes I run the sp from the same db context the sp is in.
Q2 - No the owner for our msdb db is sa, and XYZ-Domain\Administrator is the owner for the Database where the sp is on.

To Michael: The sp is not in the msdb databse, but on another database.

juancabrera
Starting Member

30 Posts

Posted - 2007-11-28 : 16:54:15

Oops my bad, sa is the owner for all databases

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-28 : 16:58:42

I believe the EXECUTE AS clause in the stored procedure only sets the user context in the database where the procedure is located.

To do what you want, you will have to create your procedure in the msdb database, and grant execute access on that procedure to the appropriate users.

CODO ERGO SUM

juancabrera
Starting Member

30 Posts

Posted - 2007-11-28 : 17:00:26

You are very right Michael. I run the stored procedure without the "Execute As" and it worked because I am an administrator. So I will have to do what you said.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources