[文章]账号执行sys.fn_hadr_backup_is_preferred判断语句所需权限

收藏

 0  606 0

汪瑞阳 2018-09-30发布

账号执行sys.fn_hadr_backup_is_preferred判断语句

所需权限及操作配置

【摘要】

1 架构环境

Ø Windows 2012R2 + SQL Server 2014 SP2

Ø AlwaysOn架构：TS-SQL-01（主节点）、TS-SQL-02（辅节点）

2 问题背景

2.1 目标需求

为了实现AlwaysOn架构下主辅节点代理作业（T-SQL语句类）的高可用性，在AG组的“Backup Preferences”设置为“Primary”的前提下，我们可以通过修改代理作业“Steps”中的SQL执行语句，通过添加执行[master].sys.fn_hadr_backup_is_preferred_replica判断语句去实现作业在主辅节点上同时启用的状态下，仅在主节点上执行有效作业任务。

2.2 T-SQL语句调整

Ø 修改前使用语句

execute PRINT ‘1’

Ø 修改后使用语句

DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('TSDB_A1'))

IF (@preferredReplica = 1)

BEGIN

execute PRINT ‘1’

END

2.3 问题描述

当代理作业的Owner账号的Server Roles为Public时，我们在修改完T-SQL语句后，在执行代理作业时，会报如下Msg 297错误。

【正文】

一、原因分析

1 报错分析

出现上述Msg 297错误，对于SQL Server的描述是由于执行账号没有VIEW SERVER STATE权限导致执行SELECT [master].sys.fn_hadr_backup_is_preferred_replica判断语句失败，因此我们需要赋予执行账号VIEW SERVER STATE权限方可执行修改后的判断语句。

除了赋予账号VIEW SERVER STATE权限外，还需要赋予执行账号对于目标数据库所在AG组的VIEW DEFINITION权限，这样应用账号可以获取指定AG组的数据结构定义数据，判断当前节点是否为主节点，否则代理作业虽然执行成功但不会输出执行结果。

例如，如果应用账号在被赋予VIEW SERVER STATE权限，而没有被赋予VIEW DEFINITION权限，我们在执行SELECT [master].sys.fn_hadr_backup_is_preferred_replica判断语句后，其只会显示命令执行成功，而不会有0或者1的输出结果。

2 权限概述

2.1 VIEW SERVER STATE

具有VIEW SERVER STATE权限后，应用账号可以查看服务器范围内的DMV（动态管理视图）和DMF（动态管理函数），动态管理视图和函数返回可以用来监视服务器实例的运行状况、诊断故障以及优化性能的服务器状态信息。

2.2 VIEW DEFINITION

具有VIEW DEFINITION权限后，应用账号能够看到被授予权限的安全对象的元数据（定义数据结构的数据）。另外，VIEW DEFINITION权限不会授予应用账号对安全对象本身的访问权限。

二、权限配置步骤

1 赋予账号VIEW SERVER STATE权限

1.1 将代理作业执行目标数据库所在AG组的“Backup Preferences”设置为“Primary”；

1.2 在Logins下找到作业Owner账号，右键账号选择属性，定位到“Securables”，找到“view server state”，在GRANT列进行勾选，点击确定；

2 赋予账号VIEW DEFINITION权限

1.3 检查作业执行的目标数据库，找到该数据库所属的AG组；

1.4 右键AG组选择属性，定位到“Permission”，点击“Search”选项；

1.5 找到要赋予权限的目标账号登录名，点击确定；

1.6 找到View definition选项，在GRANT列进行勾选，点击确定；

3 辅助副本权限配置

由于VIEW SERVER STATE和VIEW DEFINITION权限均属于实例层级，因此需要在辅助副本上进行相同配置的操作。

三、配置注意项

Ø 作业执行的目标数据库所在AG组的“备份首选项”需要是“主副本”；

Ø 作业的T-SQL语句调整完成后，目标数据库需要修改为“master”；

Ø 由于VIEW SERVER STATE和VIEW DEFINITION属于实例层级权限，因此权限配置在主副本和辅助副本上都需要进行配置。

Ø 执行账号对于目标数据库的权限，需要看执行的T-SQL语句所需权限具体进行配置。

{{post.UserNickName}}
{{post.CreateTime}}

{{post.ThumbsUpCount}} {{post.CommentCount}}条评论
编辑采纳

{{comment.UserNickName}}（{{comment.RoleName}}）回复{{comment.ReplyPeopleName}}：{{comment.Content}}

{{comment.ThumbsUpCount}} {{comment.CreateTime}} 回复删除

评论取消

评论取消
暂无回答

最近热帖: {{item.Title}}  {{item.ViewCount}}

近期热议: {{item.Title}}  {{item.PostCount}}