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

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.objects

WHERE   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_ONLY

FOR

    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_FORWARD

FOR

    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_FORWARD

FOR    

 

    SELECT schema_id,name FROM sys.tables;

 

 

OPEN table_cursor;

FETCH NEXT FROM table_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 table_cursor INTO @schema_id, @name;

 

END

 

CLOSE table_cursor;

DEALLOCATE table_cursor;

 

 

只要单个用户授权,那么使用方面方法已经OK,可是贰个连串,日常境遇那种授权景况,那么使用方面方法也会变得老大繁琐;此外,当新增表、视图、存储进程后,又须求修改相关授权脚本,那样变得不行辛勤繁琐,那么可以创立3个数据库角色,授予查看数据库对象定义的权位,然后新建登录名时,将这一个数据库角色赋予新建的登录名即可。那样之后就是有权力啥变化,只需求调动数据库角色的权力即可,不需修改登录名。对于重复性的授权工作会变得拾贰分便宜、不难。例如,必要给新建的报到名kerry授予查看数据库对象定义的权限,那么首先成立数据库剧中人物View_Definiton,然后将翻开定义的权位授予数据库角色View_Definiton,最后,创造了登录名kerry后,只需将角色View_Definiton授予kerry即可。

 

 

USE AdventureWorks2014;

GO

CREATE 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.objects

WHERE   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;

SQL Server 1

 

 

ALTER
ROLE
[View_Definiton] ADD MEMBER [kerry]

GO

相关文章