Execute job question

Question

Post reply

Execute job question

Leon Yak

SSC Journeyman

Points: 77
More actions
January 28, 2004 at 12:33 pm

#160712

Hi,
How can I grant rights to execute only one job that is owned by 'sa' to another login without giving that account rights as sysadmin.
The job uses CmdExe in the one of the steps.
And the error message is:
"Non-SysAdmins have been denied permission to run CmdExec job steps."
Thank you

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

You need configure SQL Server Agent proxy account from Agent property.

Leon Yak SSC Journeyman Points: 77 More actions · Answer 2

User will start job from the batch, so I would like go allow him to execute only ONE particular job.

Can it be done?

Thank you

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

I would create a stored proc that will execute the job grant the appropriate permissions to execute the proc to the user and whenever the user wants to execute the job he can just run the proc.

Shamless self promotion - read my blog http://sirsql.net

Leon Yak SSC Journeyman Points: 77 More actions · Answer 4

Thank you.

That what I gona do.

But would it make sence to have a job level permission?

Leon

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

Job level permissions are created by restricting access to the procedure which will execute the job.

Shamless self promotion - read my blog http://sirsql.net

Leon Yak SSC Journeyman Points: 77 More actions · Answer 6

You are right.

But, I have in the job with a few dependant steps, executing T-SQL and CmdExe commands..

Of course, everything could be done in store proc, but it seemce easier to do it as a job steps.

regards,

Leon

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 7

Sorry I'm not clarifying this right.

You can create a stored proc that simply is an sp_start_job for the job that you want to execute. Granting the permissions for that user to the proc should allow the user to start the job provided that you have set up the SQL Agent proxy account.

This means you only have to give the user access to the proc and not all of the jobs.

Shamless self promotion - read my blog http://sirsql.net

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 8

I'm using alerts to perform these kind of task.

- Cmdshell stays closed.

- dba has control on jobs running on db-servers

- alertno-ranges support portability (DRP)

Test this :

print @@servername

USE Master

go

Declare @MessageNummer int

Declare @JobName varchar(128)

Declare @DbName varchar(128)

set @MessageNummer = 60010 -- <-- adjust!!!

set @JobName = 'myjob' -- <-- adjust!!!

set @DbName = 'MyDb' -- <-- adjust!!!

--

-- dedicated ranges

-- 60000 - 60009 server1 - userdb

-- 60010 - 60499 server2 - Mydb

-- 61000 - 61499 Server4 - pubs

Declare @wrkstr1 varchar(128)

Declare @wrkstr2 varchar(128)

set @wrkstr1 = 'MYCOMPANY Requesting Job ' + @JobName + ' to be launched.'

-- add message

exec sp_addmessage @msgnum = @MessageNummer

, @severity = 10

, @msgtext = @wrkstr1

-- , @lang = 'language'

, @with_log = 'true'

-- , @replace = 'replace'

--go

set @wrkstr1 = 'MYCOMPANY_Requesting_Job_' + @JobName

set @wrkstr2 = 'Requesting Job ' + @JobName + ' to be launched.'

-- define Alert in MSDB

exec msdb.dbo.sp_add_alert @name = @wrkstr1

, @message_id = @MessageNummer

, @severity = 0

, @enabled = 1 -- 0 = disabled / 1 = enabled

, @delay_between_responses = 5 -- Seconds

, @notification_message = @wrkstr2

--, @include_event_description_in = 0

, @database_name = @DbName

--, @event_description_keyword ='Requesting Job ' + @JobName + ' to be launched.'

, @job_name = @JobName

-- , @raise_snmp_trap = raise_snmp_trap] niet in SQL 7.0

-- , @performance_condition = 'performance_condition'

--, @category_name = 'Application Events'

exec msdb.dbo.sp_help_alert

go

use MyDb

go

RAISERROR ( 60010 , 10 , 1 ) WITH log

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 9

Oh that is more cunning than cunning. I like it.

Shamless self promotion - read my blog http://sirsql.net

Leon Yak SSC Journeyman Points: 77 More actions · Answer 10

Thank you everybody,

Using the alerts to start job - is nice and elegant solution to allow the user to execute job using rights, he realy does not have.

Great! Thank you.