SQL Server Agent Jobs managed on the web and least privilege

I ran into an interesting scenario the other day at work with a SQL Server 2000 system. A developer was updating a web page that allowed business users to monitor/execute a SQL Agent Job or more to the point monitor and execute multiple jobs. This web page had been working fine for some time and they stopped working. As it turns out the permissions were changed on the account after the 3rd Party application was upgraded and the old permissions were reset. The root cause of the problem was a permissions change and the old permissions were set too high and gave too much privilege to the account. As a DBA, this frustrates me but we'll avoid that and get to the point of how to resolve the issue.

Here is the scenario...multiple SQL Agent Jobs being managed via an Intranet Web Page by the business end-users. The web page is connecting as a privileged user with sysadmin rights, this is never recommended. So how do we fix this?

The SQL Agent Jobs are using a mix of T-SQL and Operating System (CmdExec), the CmdExec requires SysAdmin rights to run. There is another option and that is to use a SQL Agent Proxy Account for the job, this account will not have SysAdmin rights and that means we can take away SysAdmin right from the other account. Now this creates another problem, now the web page throws an error saying it can't find the Jobs. The SQL Login is not a SysAdmin now and can't see the jobs, the problem is with the Owner of the jobs. The login must be the owner of the job in order to see or start/stop the job. The web page is also executing msdb.dbo.sp_help_job and the login needs to be granted the SELECT permissions on the msdb.dbo.sysjobhistory system table in order to view job history. All of this grants the least privilege to the login and helps to protect the server.

To summarize the above, here are the actions taken:
1) Create a SQL Agent Proxy Account
2) Run the job step(s) requiring higher privilege under the SQL Agent Proxy Account
3) Remove SysAdmin rights from the SQL Login
4) Grant least privilege to the SQL Login, the lowest rights required to access each database
5) Grant SELECT on msdb.dbo.sysjobhistory (System Table) to view Job History using msdb.dbo.sp_help_job (Stored Procedure)
6) Assign SQL Login as Owner of the SQL Agent Jobs being monitored and executed

For more information, check MSDN.

Permissions for sp_start_job:
http://msdn.microsoft.com/en-us/library/aa260289(SQL.80).aspx
Execute permissions default to the public role in the msdb database. A user who can execute this procedure and is a member of the sysadmin fixed role can start any job. A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.


Permissions for xp_sqlagent_proxy_account:
http://msdn.microsoft.com/en-us/library/aa260700(SQL.80).aspx
Sets or retrieves the proxy account information used by SQL Server Agent and the xp_cmdshell extended stored procedure when executing jobs or commands for users who are not members of the sysadmin fixed server role. The proxy account is a Microsoft® Windows® account in whose security context the jobs or command prompt commands are run.