x

SSIS - Security

Hi Friends,

I am working on creating a app based on (.net) which will be using Windows Athentication for Connecting to SQL Server.

Use of App:

  1. Display Jobs & Respective Steps from SQL Server Agent, by qurieng the SYSJOBS & SYSJOBSTEPS tables.
  2. Trigger the Jobs & Step Using SP_START_JOB.

Bottle neck:

  1. I want to restric the user t only See the JOBS / STEPS to which he has Access.

Can you Please suggest the Best way to create and impliment the user Security.

Thanks & regards, Sudhir Nune

more ▼

asked Jan 23, 2012 at 08:00 AM in Default

nune gravatar image

nune
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

By default normal user doesn't have access to the sysjobs and sysjobsteps tables.

However you can use EXEC dbo.sp_help_job to get list of jobs. Only jobs to which particular user has access will be listed.

For getting details about job steps you can use EXEC sp_help_jobstep 'job guid' to return steps of particular job. Again it only returns rows for job to which user has access and will fail for JOB GUID to which user doesn't has access. But as you receive only list of jobs to which user has access you can request only those steps.

Except EXEC dbo.sp_help_job you can use select * from sysjobs_view at least or SQL 2008R2 and again it will return only those jobs to which user has access.

Access to the jobs is controlled by SQL Server Agent fixed database roles in msdb as @Shawn_Melton has mentioned.

[SQLAgentUserRole] - member of that role can list and execute only jobs for which they are owners. Users are also able to create jobs.
[SQLAgentReaderRole] - member can list all jobs, but cannot execute them.
[SQLAgentOperatorRole] - member can list all jobs and also execute them. Cannot change ownership.
sysadmin - can do anything with jobs.

If user isn't member of any above mentioned groups, the not jobs will be listed.

Generally you do not need to take care about the secrutiy as SQL Server only allows operation allowed by permissions granted in above mentioned groups. It means if user is member of SQLAgentUserRole and is not owner of the job, sql server doesn't allow to execute particular job and will raise an error.
more ▼

answered Jan 23, 2012 at 11:28 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

I don't think you will be able to get around that bottleneck. With regards to using sp____start____job you will want to read over the security requirements for execute permissions on it. It involves using the [SQL Server Agent fixed database roles][2]. However executing a job is only available if the user owns the job according to documentation.

I believe to do your restrictions you will need to filter this on the application side. They may be able to execute any job but you only return the job_id for ones you want them to see and execute on. However I see a big overhead keeping that up over time.

[2]: http://msdn.microsoft.com/en-us/library/ms188283.aspx
more ▼

answered Jan 23, 2012 at 10:46 AM

Shawn_Melton gravatar image

Shawn_Melton
5.4k 20 21 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x942

asked: Jan 23, 2012 at 08:00 AM

Seen: 679 times

Last Updated: Jan 23, 2012 at 08:01 AM