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.