SQL job permission settings for non-sysadmins

Question

Post reply

SQL job permission settings for non-sysadmins

Ryan D.

SSCommitted

Points: 1595
More actions
April 16, 2010 at 2:31 pm

#219302

I'm not sure if this question has already been answered, maybe it has and I'm just searching under the incorrect keywords.
I have a non-sysadmin who wants the ability to run a job.
Multiple applications reside on this SQL farm server.
I want to limit the users ability run only jobs related to his application database.
I have set the user as the owner of the job and grant SQAgentUserRole which will only allow him rights to view and run the specific job.
However, if the job owner doesn't have the necessary permissions to the database as db_reader or more particularly db_writer the job fails.
I have read and tested the N'SQLAgentLimitedOperatorRole' option listed on http://www.sqlservercentral.com/Forums/Topic473970-359-2.aspx however this allows the user to run any job.
Other than giving the user the same rights as the application account, does anyone have any suggestions. Is there a hidden SQL job matrix table that enables users to execute the particular job.
Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

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

JeremyE SSCoach Points: 16229 More actions · Answer 1

SQL Server Agent Proxies may be what you are looking for.

http://msdn.microsoft.com/en-us/library/ms189064.aspx

Ryan D. SSCommitted Points: 1595 More actions · Answer 2

I'm familiar with using a proxy, however I dont see how to setup for a tsql step. Do I need to change the TSQL step to an SSIS package that executes a TSQL operation?

Thanks

Ryan

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Ryan D. (4/16/2010)
I dont see how to setup for a tsql step.

create a proxy account and used that account in job.is that what your are looking for ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ryan D. SSCommitted Points: 1595 More actions · Answer 4

Bhuvnesh,

The job I have has two steps.

Step 1 uses cmd to run the first step.

Step 2 uses TSQL.

I have created a Proxy to run step 1, however, since step 2 uses TSQL, the job owner requires more permissions than he should have.

My issue is that unless the jobowner owns the specific table, the TSQL fails.

Other than creating an SSISProxy and SSIS package for the job owner to run the entire job, is there any other alternatives?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

i think, you can explicitly map that user with that table.try it

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ryan D. SSCommitted Points: 1595 More actions · Answer 6

Bhuvnesh,

Are you saying that I should add the job owner to the table that is required for the TSQL statement? I'm trying to avoid unnecessary accounts being added to the database.

Darren Wallace Right there with Babe Points: 774 More actions · Answer 7

Have you considered putting the TSQL code into a stored procedure and using ownership chains.

The simplest version is to create a stored procedure using an account that has the required permissions for what you want to do (ex. dbo) then GRANT EXECUTE on the stored procedure to the lower privileged user so they can execute it. The user can now call the SPROC to do things that he doesn't have permissions to do normally, so your execute TSQL step can run as the user without any additional permissions or proxies. The only down side to this method is that the user can still execute the procedure at any time without using the job, but at least they will be restricted to only the specific actions of the stored procedure.

If you are crossing databases within your TSQL (read/write from more than one DB in the stored procedure) then it gets more complicated but hopefully you're not. You can still make this work if you are but you have to do code signing and key or certificate based users.

For more on having a stored procedure run with a different security context from the caller see the EXECUTE AS clause for CREATE PROCEDURE:

http://msdn.microsoft.com/en-us/library/ms188354.aspx

Hope that helps,

-DW

Ryan D. SSCommitted Points: 1595 More actions · Answer 8

Darren,

Thanks for your reply, I expecting this type of answer but hoping for an alternative. For what ever reason, I can't understand why developers not all are hesistant in creating stored procedures to do whatever task needs to be done. Now I just have to push the developer in fixing his code.