How do I grant rights to a SQL Server Agent?

How do I grant rights to a SQL Server Agent?

Using SQL Server Management Studio

1. In Object Explorer, expand a server.
2. Expand Security, and then expand Logins.
3. Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties.
4. On the User Mapping page of the Login Properties dialog box, select the row containing msdb.

What are proxies in SQL Server Agent?

A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

How to allow agent jobs management for non sysadmins?

Grant EXECUTE permissions for all of your compiled *_for_non_admins stored procedures. Other options are to grant the EXECUTE permissions to the existing database fixed role where the client login/group is already member. Another option that comes into my mind is to include the agent jobs names (totally or partially) on a control table.

Can a user start and stop a SQL Agent?

The user will be able to start (as well as stop, enable, and disable) any SQL Agent job on that server. (This solution turned out to satisfy the original asker.)

Can a service account manage a SQL Server Agent job?

In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the service account needs permissions on agent roles and someone will need to connect to SQL Server using the service account (run as) so they can manage the agent job. It works, but not practical.

How to create a role in MSDB for non admins?

Create a role (will be easy to manage) on the msdb database and add the logins (nominal or groups) to it. Grant EXECUTE permissions for all of your compiled *_for_non_admins stored procedures. Other options are to grant the EXECUTE permissions to the existing database fixed role where the client login/group is already member.