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.
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
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.