2

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.

4

In and of itself, no, you are probably not adding a security risk. As long as no one but a sysadmin can alter the procedure and as long as the ONLY thing the procedure can do is start a job I don't see a problem.

The risks come in with the fact that MSDB is (and is supposed to be) marked as TRUSTWORTHY. That means that by using EXECUTE AS OWNER you are able to impersonate the SERVER level permissions. In this case since it's sa that means you could create a procedure to grant another user sysadmin.

That does mean you do want to limit permissions to MSDB though.

  • 1
    That's what I thought... I've just been reading your post - interesting read! – Max Vernon Apr 28 '16 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.