I've been tasked with creating a system that allows non-privileged users to start and stop SQL Server Agent jobs. I'm creating stored procedures in the dbo
schema in the msdb
database using the WITH EXECUTE AS OWNER
construct to run EXEC dbo.sp_start_job <JobID>;
, etc.
The Books Online topic about EXECUTE AS
says this is a "best practice":
Specify a login or user that has the least privileges required to perform the operations defined in the module. For example, do not specify a database owner account unless those permissions are required.
In my case, EXECUTE AS OWNER
indicates [sa]
, since those modules are in the dbo
schema, in the msdb database, which is always owned by [sa]
.
Am I inadvertently creating a security risk by doing this?
I'm not passing in any parameters that could be used in a SQL injection attack since the only parameter ever being passed in is a UNIQUEIDENTIFIER
representing the job affected.
The user running the stored procedures that have EXECUTE AS OWNER
have no access to the msdb database aside from membership in a database role I've explicitly created which GRANTs
them the EXECUTE
permission on the stored procs.
One of the conditions of this project is the users in question should not be able to run the SQL Server Agent GUI tools in any way. This rules out giving them membership in the built-in SQLAgentUserRole
role. I'd also rather not modify the permissions on dbo.sp_start_job
, etc, since I'm trying to not modify anything that SQL Server itself depends on (other than obviously adding items to the msdb database itself).
The code in question is here.