I added a user to the msdb database and gave him the SQLAgentUserRole but that gave him access to all the jobs, which is not what I wanted. This is the work around I followed....
USE msdb
GO
CREATE PROC dbo.Start_Job_MyJobThatAGroupControls
-- Executes as the database owner, for msdb that is 'sa'
WITH EXECUTE AS OWNER
AS
-- Hardcoding the job name limits this procedure's control
EXEC dbo.sp_start_job @job_name = 'MyProductionJobName'
GO
-- Grant Execute rights to your selected users
GRANT EXECUTE ON Start_Job_MyJobThatAGroupControls TO <usernames>
GO
This way the user will not have direct access to the job MyProductionJobName but through this wrapper sp it can run the job.
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Tuesday, June 18, 2013
Permission to run a specific SQL job to a user
Labels:
Administration,
execute,
msdb,
permissions,
sql 2008,
SQL Agent Jobs
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment