job execute permission

Question

Viewing 2 posts - 1 through 2 (of 2 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I'm assuming that you want the user to be able to run a specific job and not all jobs. If I'm correct, then you can do it using the fallowing stpes:

1)Create a procedure that uses sp_start_job and starts the specific job

2)Create a certificate

3)Add signature by the certificate to the procedure.

4)Backup the certificate and then restore it in MSDB database.

5)In msdb create a user from the certificate

6)Grant permission to authenticate to the user

7)Grant the user permissions to execute sp_start_job

You can use the script bellow as basis to get it done.

--Write you DB name here

use WriteYourDBNameHere;

go

create certificate MyCert

ENCRYPTION BY PASSWORD = '9B22AE54-1E44-4E9F-81C7-EB1E119353D2'

with subject = 'RunningAJob';

go

--Write the name of the procedure instead of dbo.ProcRunningJob

ADD SIGNATURE TO OBJECT::dbo.ProcRunningJob

BY CERTIFICATE [MyCert]

WITH PASSWORD = '9B22AE54-1E44-4E9F-81C7-EB1E119353D2';

go

alter certificate [MyCert]

remove private key;

go

backup certificate [MyCert]

to file='c:\temp\agentProxy.cer';

go

use msdb

go

create certificate [MyCert]

from file='c:\temp\agentProxy.cer';

go

create user [UserRunningJobs]

from certificate [MyCert];

go

grant authenticate to [UserRunningJobs];

grant execute on msdb.dbo.sp_start_job to [UserRunningJobs];

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/