- 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;
来源: http://www.bubuko.com/infodetail-2712158.html