How to grant access to view jobs

[trpnbillie]

Greetings!

Is it possible to grant limited access to view and modify jobs?

I have some sql jobs set up.. the jobs run queries against a database called "Export".

I want to grant a new user full access to the export database and also allow him to make changes to these jobs. At the same time, I don't want him to have access to the other databases on the server or be able to modify non "Export" dts packages or jobs.

First part - check! I created a login for him. Gave him dbo permissions to the "Export" database and nothing else.

Second part - no luck! How can i get him access to the "Export" sql jobs?

I appreciate any help!!

 

[jbenson001]

I don't think you can do that. It has to do with SQL Server roles, so if you give them and admin role, they will be able to modify any job. This is because jobs are at the server level, not the DB level.
I would like to know if anyone else has a way to do it, but I don't think you can.

Jim

 

[trpnbillie]

Thanks Jim!

Do you know if DTS packages are also at the server level? The new question being: Can I allow the user to access all sql jobs but none of the DTS packages?

 

[jbenson001]

Same issue there, they are at the server level.

 

[SQLSister]

However if you have more than one server, you can put all the DTs packages on the server the person doesn't have access to. A DTS package can run from one server from a different server if it is set up that way. The servers do have to see each other though. I used to keep all my DTS packages on the devlopment server even though some of them were running specifically on the production server and not dev at all.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jbenson001]

Great point SQLSister!

 

[SQLDenis]

Just make sure you don't keep the connection (local) in your DTS packages since you might have data from dev servers going out to the real world

It happens, I know firsthand haha

Once you move them over put the server name/IP in the connection right away

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[trpnbillie]

Thanks folks - this site is niiiiice!