本文详细解析了SQL Server Agent的三种固定数据库角色：SQLAgentUserRole、SQLAgentReaderRole和SQLAgentOperatorRole，以及sysadmin角色的权限差异。针对无法查看SQL Server Agent组件的问题，文章提供了解决方案，并介绍了各角色的具体权限，帮助理解SQL Server Agent的安全控制。
开发反馈需要看job执行情况，但登录之后发现看不到SQL Server Agent组件，需要帮忙处理。
一、 解决方法
登上服务器检查了下SQL Server Agent服务运行正常，使用管理员账号登录能看到该组件。
可以猜到这个问题应该跟权限有关，查到官方文档介绍如下：
SQL Server使用msdb数据库管理Agent 对象权限，msdb内置三个数据库角色，权限从小到大依次是：SQLAgentUserRole -> SQLAgentReaderRole -> SQLAgentOperatorRole（大权限角色包含小权限）。如果用户不是这三个角色或sysadmin角色中的成员，则无法通过SSMS的Object Explorer看到及使用SQL Server Agent组件。
SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
When users who are not members of one of these roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent.
如果只需要看，给到SQLAgentReaderRole就够了；如果需要启停job，需要给到SQLAgentOperatorRole
刷新再看，发现可以看到agent组件了
二、 相关角色简介
解决完问题再来看下官方文档对相关角色的描述：
SQLAgentUserRole 角色被授予操作Agent的最小权限，仅对自己创建的local jobs和job schedules有操作权限。无multiserver jobs (master and target server jobs) 操作权限，也不能修改Job的所有权关系
SQLAgentReaderRole 角色，额外具有Multiserver jobs的查看权限，其余和SQLAgentUserRole相同。如果用户被授予该权限，在SSMS的agent组件中只能看到job部分。
SQLAgentOperatorRole 角色，额外具有查看Alert列表和属性的权限，其余和SQLAgentReaderRole 相同。如果用户被授予该权限，在SSMS的agent组件中能看到所有部分。
三个角色都对自己创建的Job具有操作权限，但都不能修改Job的所有权
sysadmin 角色，具有SQL Server Agent的全部权限，能够修改Job的所有权。
三、 详细权限列表
1. SQLAgentUserRole
| Action | Operators | Local jobs (owned jobs only) | Job schedules (owned schedules only) | Proxies |
|---|---|---|---|---|
| Create/modify/delete | No | Yes Cannot change job ownership. | Yes | No |
| View list (enumerate) | Yes Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. | Yes | Yes | Yes List of proxies only available in the Job Step Properties dialog box of Management Studio. |
| Enable/disable | No | Yes | Yes | Not applicable |
| View properties | No | Yes | Yes | No |
| Execute/stop/start | Not applicable | Yes | Not applicable | Not applicable |
| View job history | Not applicable | Yes | Not applicable | Not applicable |
| Delete job history | Not applicable | No Members of SQLAgentUserRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs. | Not applicable | Not applicable |
| Attach/detach | Not applicable | Not applicable | Yes | Not applicable |
2. SQLAgentReaderRole
| Action | Operators | Local jobs | Multiserver jobs | Job schedules | Proxies |
|---|---|---|---|---|---|
| Create/modify/delete | No | Yes (owned jobs only) Cannot change job ownership. | No | Yes (owned schedules only) | No |
| View list (enumerate) | Yes Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. | Yes | Yes | Yes | Yes List of proxies only available in the Job Step Properties dialog box of Management Studio. |
| Enable/disable | No | Yes (owned jobs only) | No | Yes (owned schedules only) | Not applicable |
| View properties | No | Yes | Yes | Yes | No |
| Edit properties | No | Yes (owned jobs only) | No | Yes (owned schedules only) | No |
| Execute/stop/start | Not applicable | Yes (owned jobs only) | No | Not applicable | Not applicable |
| View job history | Not applicable | Yes | Yes | Not applicable | Not applicable |
| Delete job history | Not applicable | No Members of SQLAgentReaderRole must explicitly be granted the EXECUTE permission on sp_purge_jobhistory to delete job history on jobs that they own. They cannot delete job history for any other jobs. | No | Not applicable | Not applicable |
| Attach/detach | Not applicable | Not applicable | Not applicable | Yes (owned schedules only) | Not applicable |
3. SQLAgentOperatorRole
| Action | Alerts | Operators | Local jobs | Multiserver jobs | Job schedules | Proxies |
|---|---|---|---|---|---|---|
| Create/modify/delete | No | No | Yes (owned jobs only) Cannot change job ownership. | No | Yes (owned schedules only) | No |
| View list (enumerate) | Yes | Yes Can get list of available operators for use in sp_notify_operator and the Job Properties dialog box of Management Studio. | Yes | Yes | Yes | Yes |
| Enable/disable | No | No | Yes SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. | No | Yes SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. | Not applicable |
| View properties | Yes | Yes | Yes | Yes | Yes | Yes |
| Edit properties | No | No | Yes (owned jobs only) | No | Yes (owned schedules only) | No |
| Execute/stop/start | Not applicable | Not applicable | Yes | No | Not applicable | Not applicable |
| View job history | Not applicable | Not applicable | Yes | Yes | Not applicable | Not applicable |
| Delete job history | Not applicable | Not applicable | Yes | No | Not applicable | Not applicable |
| Attach/detach | Not applicable | Not applicable | Not applicable | Not applicable | Yes (owned schedules only) | Not applicable |
参考
https://www.cnblogs.com/ljhdo/archive/2017/11/24/5177423.html
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
