首页> 数据库> 账号执行sys.fn_hadr_backup_is_...

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

收藏
0 606 0

SQL SERVER

账号执行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语句所需权限具体进行配置。

数据库
最近热帖
{{item.Title}} {{item.ViewCount}}
近期热议
{{item.Title}} {{item.PostCount}}