January 6, 2012 at 7:48 am
I have a SQL Server 2005 running on Windows 2003 .
I've created a stored procedure as:
USE database_name
GO
CREATE PROC dbo.run_job
AS WITH EXECUTE AS 'domain\login'
EXEC msdb.dbo.sp_start_job @job_name = 'job_name'
The domain\login is a sysadmin account and has full dbo permissions in the msdb and the user_database.
When trying to run the stored procedure from a query window I get the following error message: Msg 229, Level 14, State 5, Procedure sp_start_job, Line 1
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
Yet, when I just run the EXEC msdb.dbo.sp_start_job @job_name = 'job_name' the command it works fine and If I alter the stored procedure and remove the WITH EXECUTE AS 'domain\login' it works fine. I've also tried EXECUTE AS OWNER with the same results.
The reason I'm using the WITH EXECUTE AS 'domain\login'/EXECUTE AS OWNER is I'm trying to give a user the permission to run just this job but I don't want to give them any more permissions than that. So if there's a way to fix the stored procedure or use another method I'm all ears.
January 6, 2012 at 8:07 am
- Do you execute msdb.dbo.sp_start_job @job_name = 'job_name' being connected as 'domain\login' ?
- What about trustworthy option in the database where the stored procedure is ?
January 6, 2012 at 8:40 am
It's been executed both as domain\login (me/sysadmin) and as the user I'm trying to create the stored procedure for. Regardless of who executes the stored procedure the error message is the same.
As far as the Trustworthy option is concerned it's currently set to FALSE in the database the stored procedure was created in. However, in SSMS, in the properties for the database that option is grayed out. Is this an Enterprise Edt. property
January 6, 2012 at 8:59 am
No, it's available in any version but not editable via SSMS
You just have to run :
ALTER DATABASE YourDb SET TRUSTWORTHY ON
to try if it helps
January 6, 2012 at 9:17 am
I moved the stored procedure from the user database to the msdb database. Now when I execute the store procedure it works. I haven't been able to have the other user test this yet as he's been busy this morning. I'll post the results hopefully later today.
Any ideas why moving it (at least for me) solved the permissions issue? You think this is related to the TRUSTWORTHY option?
January 6, 2012 at 9:27 am
Why don't you just try to modify the trustworthy option on your db to be sure ?
And yes, I think this could be the issue.
January 6, 2012 at 9:58 am
No need, it now works for the other user as well. When I get the chance I'll test it out and see if that is what was causing the problem. If after testing it and this post is still active I'll update it.
Thanks for the hint on the TRUSTWORTHY option, it's always cool to learn something new!
January 7, 2012 at 6:40 am
Don’t you want to know what the issue was & what fixed it? If you have any ideas on it please share with us. It will close the thread gracefully.
January 9, 2012 at 9:34 am
The EXECUTE AS Clause sets the execution context to a Database User, not a Server Login. The distinction implies that the Database User executing the proc must exist in all databases to which the proc refers, the Database User in each database must be authorized to perform all actions the called proc attempts and cross-database ownership chaining must be on.
From the BOL article on the EXECUTE AS Clause http://msdn.microsoft.com/en-us/library/ms188354.aspx (bold added by me):
By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module.
Setting the non-msdb database's TRUSTWORTHY flag ON should not be necessary. Further, I would not recommend looking into that approach as it carries many security concerns along with turning it ON. I would recommend signing the proc with a certificate, adding the certificate to both databases and then granting exec on the proc to whomever requires it. Here is a good article that explains that process:
http://www.sommarskog.se/grantperm.html#certcrossdb
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply