SQL 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