June 8, 2011 at 9:21 pm
I have an application that times out SQL after 30 seconds. Because of this, I'd like to run a stored procedure via a SQLJob. This works fine when I run as an administrator, however, my everyday users do not have access to run SQL Jobs. Is there a way to change users before the procedure is run? I tried "with execute as", but this doesn't seem to work.
CREATE PROCEDURE runmysqljob
with execute as 'dbo'
AS
BEGIN
EXECUTE msdb.dbo.sp_start_job @job_name = 'DEVTEST_SQLJOB'
END
GO
June 9, 2011 at 10:40 am
You can use the MSDB fixed database roles to grant your users the ability to run jobs. The SQLAgentUserRole can be granted to the users, then have the specific job owned by the AD group so they can run that job and no other job.
Also, regarding the "with Execute" statement, I think you need to give it a specific windows or sql login. I don't think "dbo" will work. Check out the "Execute AS clause" in Books Online for more details.
Speaking as a paranoid DBA, I would never give "dbo" perms on any WITH EXECUTE AS statement just as a matter of general principal. Least permissions possible is always the way to go.
June 9, 2011 at 12:53 pm
Thank you for the response. I'll read up on the "with execute as" some more.
Our application creates the SQL users and I don't want to have to go into SQL and change their security level each time a new user is added which is why I was hoping the "with execute as" would resolve my security issue.
June 10, 2011 at 2:45 am
It occurs to me to wonder if the SQL users even have permissions to run your proc. If they don't, then EXECUTE AS won't do you any good.
If this is the case, then you're going to have to update the users every time a new one is added anyway, to give it execute permissions on the proc.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply