SQLTeam.com | Weblogs | Forums

Granting permission directly on sp_start_job


Does anyone have experience granting EXECUTE permission directly on this system stored procedure? I know Books Online says to use one of the fixed database roles in msdb but the highest of those (operator) is very broad and a big security hole. I'm just not excited about granting permissions directly on a system stored procedure.

Further, did it work like you expected? Did you find any other permissions issues? Did internal audit have any concerns?


Is your aim to allow users to be able to start jobs?


Can you Execute it from an Sproc of your own, and control the permissions TO that? Presumably you can execute sp_start_job, from within your own Sproc, with some sort of impersonation?


There's a team of a few people that would be able to start and stop jobs. I've thought through the options on having credentials and stored procedures that execute as those credentials. Or some other type of wrapper around it.

Books Online says the way to grant permissions is through roles which are too broad. I'm hoping someone has just granted permission on sp_start_job and not had any issues. It seems to work but I'm not sure if there are any issues lingering around just out of sight.