Running SQL Server Jobs using a Proxy Account

In most companies, roles and responsibilities are clearly
defined for the various teams, whether it is the database team, application
team or the development team. In some cases, the application team might own a
number of jobs but they may not have direct access or the high level privileges
required to run it on the server and would mostly depend on the database
administrators to run jobs and provide  the output file or error message. In
such circumstances proxy accounts are a useful tool to allow  teams to work
independently and have limited access to the SQL Server instance.

For the purposes of this article we will demonstrate how to
use a proxy account for running a database maintenance job. We can consider this
as an example similar to an application team owning a number of jobs or
packages but requiring  the help of system or database administrators to run
their jobs.

1) First create a maintenance plan. This plan would create a
job that will take the backup of a test database. In this article, I assume
that you are aware of the steps required to create a maintenance plan.

2) Create a new login ‘testlogin’ on the server, retain
the default privileges. At this point, we will not grant any extra privileges
to this login.

3) Go to the SQL Server Agent job which was created   in
step (1). In this case, the job name is ‘Maintenancetest.Subplan_1’.
Just make sure, you change the owner of this job to ‘testlogin’, the login
which you created in step (2). Refer to the screenshot below:

4) As a first step for setting up a proxy account, we need
to create a credential. Navigate to the Security node and right click on
Credentials: 

In  the new credential window, provide an appropriate
credential name and in ‘identity’, enter the details of a windows user and password.

5) The  job step that gets created after configuring the
maintenance plan (as in step 1) is of type ‘SQL Server Integration Services
Package’. We can view this when we click on the Edit job step option. New job
steps that get created will fall under one of the 11 SQL Server Agent subsystems
  which can be seen when clicking  on the ‘type’ drop down menu as shown below:

Now, try logging into the SQL Server instance using the
login ‘testlogin’ which we created in step (2), we wouldn’t be able to
view any jobs as we didn’t grant the required privileges. In order to provide
the access, we need to consider adding this login to one of the three roles
present in the msdb database meant for this purpose.

  • SQLAgentOperatorRole
  • SQLAgentReaderRole
  • SQLAgentUserRole

We need to be cautious in adding the login to the
SQLAgentOperatorRole as this has the maximum privilege. In this case, we will
make ‘testlogin’, member of the SQLAgentReaderRole. Right click on SQL login
properties and make the change as shown below:

Now try logging on to the SQL server instance using the ‘testlogin’,
you will be able to view all the jobs on this server.

Continues…

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |