SQL Server中授予用户查看对象定义的权限

root

SQL Server中授予用户查看对象定义的权限

在SQL Server中，有时候需要给一些登录名（用户）授予查看所有或部分对象（存储过程、函数、视图、表）的定义权限存。如果是部分存储过程、函数、视图授予查看定义的权限，那么就像下面脚本所示，比较繁琐：

GRANT VIEW DEFINITION ON  YOUR_PROCEDURE TO   USERNAME;

GRANT VIEW DEFINITION ON  YOUR_FUNCTION TO USERNAME;

GRANT VIEW DEFINITION ON  YOUR_VIEW TO USERANEM;

.....................................................

如果是批量授权，那么可以使用下面脚本生成授权脚本。然后执行生成的脚本：

USE DatabaseName;GO---给用户授予查看存储过程定义的权限 DECLARE @loginname VARCHAR(32); SET @loginname='[eopms_reader]' SELECT  'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.'        + QUOTENAME(name) + ' TO ' + @loginname + ';'FROM    sys.procedures;   --给用户授予查看自定义函数定义的权限 SELECT  'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.'        + QUOTENAME(name) + ' TO ' + @loginname + ';'FROM    sys.objectsWHERE   type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',                       'AGGREGATE_FUNCTION' );  --给用户授予查看视图定义的权限SELECT  'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.'        + QUOTENAME(name) + ' TO ' + @loginname + ';'FROM    sys.views;  --给用户授予查看视表定义的权限SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id)       + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.tables;

如果你想直接执行脚本，不想生成授权脚本，那么可以使用下面脚本实现授权。当然前提是你选择所要授权的数据库（USE DatabaseName)

DECLARE @loginname VARCHAR(32);DECLARE @sqlcmd    NVARCHAR(MAX);DECLARE @name  sysname;DECLARE @schema_id  INT;SET @loginname='[kerry]'  DECLARE procedure_cursor CURSOR  FORWARD_ONLYFOR    SELECT  schema_id, name    FROM   sys.procedures;  OPEN procedure_cursor; FETCH NEXT FROM procedure_cursor INTO @schema_id, @name;   ---给用户授予查看存储过程定义的权限 WHILE @@FETCH_STATUS = 0 BEGIN  SET @sqlcmd= 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.'        + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd;EXEC sp_executesql @sqlcmd; FETCH NEXT FROM procedure_cursor INTO @schema_id, @name; END CLOSE procedure_cursor; DEALLOCATE procedure_cursor;  DECLARE function_cursor  CURSOR FAST_FORWARD FOR     SELECT schema_id, name    FROM sys.objects    WHERE type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',                       'AGGREGATE_FUNCTION' );  --给用户授予查看自定义函数定义的权限 OPEN function_cursor; FETCH NEXT FROM function_cursor INTO @schema_id,@name;  WHILE @@FETCH_STATUS = 0 BEGIN  SET  @sqlcmd= 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.'        + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd;EXEC sp_executesql @sqlcmd; FETCH NEXT FROM  function_cursor INTO  @schema_id, @name; END CLOSE function_cursor;DEALLOCATE function_cursor;  DECLARE view_cursor CURSOR FAST_FORWARDFOR    SELECT schema_id, name FROM sys.views;  OPEN view_cursor; FETCH NEXT FROM view_cursor INTO @schema_id, @name; WHILE @@FETCH_STATUS = 0 BEGIN  --给用户授予查看视图定义的权限SET @sqlcmd= 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.'        + QUOTENAME(@name) + ' TO ' + @loginname + ';' --PRINT @sqlcmd;EXEC sys.sp_executesql @sqlcmd; FETCH NEXT FROM view_cursor INTO @schema_id, @name; END CLOSE view_cursor;DEALLOCATE view_cursor;   DECLARE table_cursor CURSOR FAST_FORWARDFOR         SELECT schema_id,name FROM sys.tables;  OPEN table_cursor;FETCH NEXT FROM table_cursor INTO @schema_id, @name; WHILE @@FETCH_STATUS = 0BEGIN     SET @sqlcmd ='GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(@schema_id) + '.'            + QUOTENAME(@name) + ' TO ' + @loginname + ';'        --PRINT @sqlcmd;    EXEC sys.sp_executesql @sqlcmd;     FETCH NEXT FROM table_cursor INTO @schema_id, @name; END CLOSE table_cursor;DEALLOCATE table_cursor;

如果单个用户授权，那么使用上面方法已经OK，但是一个系统，经常遇到这种授权情况，那么使用上面方法也会变得非常繁琐；另外，当新增表、视图、存储过程后，又需要修改相关授权脚本，这样变得非常麻烦繁琐，那么可以创建一个数据库角色，授予查看数据库对象定义的权限，然后新建登录名时，将这个数据库角色授予新建的登录名即可。这样以后即使有权限啥变化，只需要调整数据库角色的权限即可，不需修改登录名。对于重复性的授权工作会变得非常方便、简单。例如，需要给新建的登录名kerry授予查看数据库对象定义的权限，那么首先创建数据库角色View_Definiton，然后将查看定义的权限授予数据库角色View_Definiton，最后，创建了登录名kerry后，只需将角色View_Definiton授予kerry即可。

USE AdventureWorks2014;GOCREATE ROLE [View_Definiton] AUTHORIZATION [dbo]GO      DECLARE @loginname VARCHAR(32); SET @loginname='View_Definiton' SELECT  'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.'        + QUOTENAME(name) + ' TO ' + @loginname + ';'FROM    sys.procedures;   --给用户授予查看自定义函数定义的权限 SELECT  'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.'        + QUOTENAME(name) + ' TO ' + @loginname + ';'FROM    sys.objectsWHERE   type_desc IN ( 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION',                       'AGGREGATE_FUNCTION' );  --给用户授予查看视图定义的权限SELECT  'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) + '.'        + QUOTENAME(name) + ' TO ' + @loginname + ';'FROM    sys.views;  --给用户授予查看视表定义的权限SELECT 'GRANT VIEW DEFINITION ON ' + SCHEMA_NAME(schema_id) +'.'      + QUOTENAME(name) + ' TO ' + @loginname + ';' FROM sys.tables;

ALTER ROLE [View_Definiton] ADD MEMBER [kerry]

GO