July 18, 2016 at 9:09 am
Hi,
How do I grant access to a user who is not a user from 'sa' group to execute just one job : Job1 ? I have used these commands below but now the user has access to execute/view all jobs. How do I limit to just one that I need him to view and execute.
USE [msdb]
GO
CREATE USER [ExecuteTransformationJob] FOR LOGIN [DIR\User1]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'ExecuteTransformationJob'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'ExecuteTransformationJob'
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'ExecuteTransformationJob'
GO
Thanks,
PSB
July 18, 2016 at 10:15 am
You can't easily do this. Jobs are at the row level.
What you could do is create your own procedures in msdb (script and save in a VCS for DR purposes) that read this job, or execute this job, and then grant a role the rights for your procs, adding the user to a role.
July 18, 2016 at 10:48 am
Can you provide me with a sample procedure or some helpful links ?
July 18, 2016 at 11:09 am
I assigned the user permission to start job .
How does he know when it completes ?
ALTER PROC usp_StartTransformationJob
@MyJobName sysname = 'Transformation'
AS
DECLARE @ReturnCode tinyint -- 0 (success) or 1 (failure)
EXEC @ReturnCode=msdb.dbo.sp_start_job @job_name=@MyJobName;
RETURN (@ReturnCode)
GO
July 18, 2016 at 3:16 pm
OK, sorry, was busy with other work.
First, if you're doing this for users, you're going to find yourself in a bind at some point. Users leave companies, and new users come.
So, set up a role first, and a group in Exchange/email, for notifications.
I'd say you are on the right track for this with your code below. Build a procs that can do these things:
- start job
- query job
- stop job
Grant rights to a role to execute your procs and add the user to a role. You can add notifications to a job step or to the job (I think, don't have a 2005 instance handy) that send a note to a group in your email system (or an alias). Map that to your user.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy