security - How can I grant a user permission to manage an SQL Server Agent job if they are not a member of the sysadmin role? -

admin管理员组

文章数量:1241085

I have been struggling to grant a user permission on an SQL Server Agent job. Since making them a member of the sysadmin role is not an option, I tried changing the job owner to this user, then I make them a member os sqlserveroperator agent.

However, they still cannot create new job steps or modify existing ones. Then I did some reaserch, to find that I must grant the user access to use the proxy for job steps, but nothing changes.

I have been struggling to grant a user permission on an SQL Server Agent job. Since making them a member of the sysadmin role is not an option, I tried changing the job owner to this user, then I make them a member os sqlserveroperator agent.

However, they still cannot create new job steps or modify existing ones. Then I did some reaserch, to find that I must grant the user access to use the proxy for job steps, but nothing changes.

• sql-server
• security
• permissions
• database-administration
• sql-server-agent

Share Improve this question Follow edited 2 days ago Thom A 95.7k1111 gold badges6060 silver badges9292 bronze badges asked 2 days ago zahreddinezahreddine 7511 silver badge77 bronze badges 4

• 1 Does this help? dba.stackexchange/questions/132667/… – Bart McEndree Commented 2 days ago
• You're probably smoked, unless you create some wrapper procedures which execute as some admin user, but that will disallow using sql server agent gui to manage things. You should be able to add jobs as non-admin user if you're owner of the job though i think? – siggemannen Commented 2 days ago
• 1 This might help learn.microsoft/en-us/ssms/agent/… – Bart McEndree Commented 2 days ago
• @BartMcEndree That really work, thank you so much you made my day. – zahreddine Commented 2 days ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

From https://learn.microsoft/en-us/ssms/agent/configure-a-user-to-create-and-manage-sql-server-agent-jobs?view=sql-server-ver16

To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

Using SQL Server Management Studio

To add a SQL login or msdb role to a SQL Server Agent fixed database role

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.

5. Under Database role membership for: msdb, check the appropriate SQL Server Agent fixed database role.

To configure a proxy account to create and manage SQL Server Agent job steps

1. In Object Explorer, expand a server.

2. Expand SQL Server Agent.

3. Right-click Proxies and select New Proxy.

4. On the General page of the New Proxy Account dialog, specify the proxy name, credential name, and description for the new proxy. Note that you must create a credential first before creating a SQL Server Agent proxy. For more information about creating a credential, see How to: Create a Credential and CREATE CREDENTIAL (Transact-SQL).

5. Check the appropriate subsystems for this proxy.
    Operating system (CmdExec)
    SQL Server Analysis Services Query
    SQL Server Analysis Services Command
    SQL Server Integration Services Package
    PowerShell

6. On the Principals page, add or remove logins or roles to grant or remove access to the proxy account.

本文标签：