用SQLServer2008策略审核存储过程书写规范

众所周知SQL2008的策略可以审核存储过程的命名规范，那存储过程内容的规范呢？…

用SQLServer2008策略审核存储过程书写规范

作者：Jerrynet 背景

关于SQLServer2008的策略管理，网上大部分的文章都是以存储过程的命名规范为例：强制要求存储过程的名字以特定的字符串开头，如usp_等等。文章都写得挺好的，图文并茂介绍得很详细。

我在想是否也可以把策略的管理延伸到存储过程内容的书写规范呢？

答案是肯定的。接下来就为大家介绍下主要的过程和一些注意事项。

主要思路

在日常的数据库维护中，经常会出现这样的情况：找到了某个存储过程有性能问题需要改，无奈却找不到写的人(我们公司的存储过程都是程序员自己搞定的)；自己改吧，改好了又没法充分验证功能绝对问题。

基于上面的情况，我们规定了每个存储过程都要标明作者和功能说明。格式类似于：

-- 作者: Jerrynet

-- 说明:修改××表中对应××的信息，在××地方调用

简单就是美！我们只要看下存储过程的定义文本中是否有“作者”、“说明”这几个词就可以了。

关键步骤

目标确定了，我们看下如何实现。

首先我们要操作的对象是存储过程，于是我们在【管理】->【策略管理】->【方面】->【存储过程】，单击右键选择”新建条件”如下图：

点击弹出如下的“创建新条件”对话框：

在打开的条件窗口中输入名称，在“表达式”中的字段栏中，没有直接的一个属性是表示存储过程的定义文本的，只有一些像过程名称、过程所有者、是否加密等属性。

那有没有替代的办法得到存储过程的定义文本的？答案就是ExecuteSQL()！

先看下官方对于该函数的简单介绍：

函数签名：Variant ExecuteSQL (String returnType, String sqlQuery)

函数说明：对目标服务器执行 Transact-SQL 查询。

参数：r eturnType - 指定 Transact-SQL 语句返回的数据类型。

sqlQuery - 包含要执行的查询的字符串。

示例： ExecuteSQL ('Numeric', 'SELECT COUNT(*) FROM msdb.dbo.sysjobs') <> 0

有了变通的方法，剩下的任务就是获取某个过程的定义文本了。主要的语句如下，如果能找到符合条件的语句，就显示OK，否则就返回空结果集。

SELECT'OK'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE (ROUTINE_DEFINITION LIKE'%作者%'or ROUTINE_DEFINITION LIKE'%Author%') AND

(ROUTINE_DEFINITION LIKE'%说明%'or ROUTINE_DEFINITION LIKE'%Description%') AND

ROUTINE_TYPE='PROCEDURE'AND ROUTINE_NAME=@@ObjectName

如果结果集为空就表示存储过程没有满足我们的要求，就输出Error，用IsNull即可。点击上图的红框出，在“高级编辑”窗口输入代码。最终的高级编辑中的效果，如下图

值得注意的是，这里的过程名不能用属性@Name，而是用@@ObjectName。

手工评估一下测试数据库，如果没有添加必要注释的存储过程就会出现错误提示。点击详细信息中的查看，还可以看到实际值和预期值不符。详见下图所示：

注意事项

1.存储过程名用变量@@ObjectName来替换

2.ExecuteSQL函数的sqlQuery参数，原来用到单引号的地方，用两个单引号来转义

3.ExecuteSql 不支持OnChange模式，也就无法禁止或者实时记录违规操作，不免有些遗憾

4.如果评估模式选“按计划”时，会出现如下的错误信息：

System.Data.SqlClient.SqlException: 服务器主体"##MS_PolicyTsqlExecutionLogin##" 无法在当前安全上下文下访问数据库"DB_Test"。

原因就是登录名"##MS_PolicyTsqlExecutionLogin##"的权限不够，执行下面这段代码授权即可。

USE[DB_Test]

GO

CREATE USER[##MS_PolicyTsqlExecutionLogin##]FOR LOGIN

[##MS_PolicyTsqlExecutionLogin##]

GO

ALTER USER[##MS_PolicyTsqlExecutionLogin##]WITH DEFAULT_SCHEMA=[dbo]

GO

EXEC sp_addrolemember N'db_accessadmin',N'##MS_PolicyTsqlExecutionLogin##'

GO

在策略管理方面，SQLServer没有提供给我们所有可能用得到的属性，而是给我们可以发挥更大空间的函数。给了我们一箩鱼的同时，又给了我们捕鱼的鱼具，这就是SQLServer策略管理!

【出自/jerrynet，转载请注明作者出处】