1 comment to Proxy permissions in a sql server agent job

This part of the (weak) SQL Agent security. The basic logic behind the concept of SQL Agent proxies seems to be:

If you want a non-DBA to be able to create/edit/run SQL Agent Jobs, you put them into the SQLAgentUserRole. They own the jobs they create, can only work with jobs that they own. You obviously don’t want someone like that being able to run jobs with the SQL Agent service account, so you can create proxy accounts and limit which ones they can use–but they have to use one of them. Now a different person can be in the SQLAgentUserRole, with a different set of proxies with permissions suitable for that person.

That’s the basic logic behind the concept of SQL Agent proxies. Unfortunately, the owner of a job has to be a login (SQL or Windows user), and can’t be a role (e.g., for a development group).

A similar logic exists for the SSIS packages, using the db_ssisltduser msdb role, except that now it is (1) an msdb role instead of a login, (2) it’s the package’s read-role/write-role instead of “owner,” and (c) there is no (documented) way to set up the access except the SSMS GUI.

If you can just make all jobs owned by sa it’s really easy. I just use proxies when a step (a) runs code provided by a programmer, or (b) needs to access resources outside of the database server.

Hope this helps.

Search:

Subscribe

Leave Blank:Do Not Change:

Your email:

Cup of coffee

If you like this site, you can buy me a cup of coffee by donating any amount (in USD).

Powered by Good Will. (Will is the person who offers his sympathy to others together with ideas, but who does not give any guarantee about the completeness of the concepts or the immaculate perfection of the scripts on this site. Use this site to grasp SQL Server concepts and to get knowledge. )