SQL Server中授予用户查看对象定义的权限
SQL Server中授予用户查看对象定义的权限
摘要: SQL Server中授予用户查看对象定义的权限 在SQL Server中,有时候需要给一些登录名(用户)授予查看所有或部分对象(存储过程、函数、视图、表)的定义权限存。如果是部分存储过程、函数、视图授予查看定义的权限,那么就像下面脚本所示,比较繁琐: GRANT VIEW DEFINI...
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,但是一个系统,经常遇到这种授权情况,那么使用上面方法也会变得非常繁琐;另外,当新增表、视图、存储过程后,又需要修改相关授权脚本,这样变得非常麻烦繁琐,那么可以创建一个数据库角色,授予查看数据库对象定义的权限,然后新建登录名时,将这个数据库角色授予新建的登录名即可。这样以后即使有权限啥变化,只需要调整数据库角色的权限即可,不需修改登录名。对于重复性的授权工作会变得非常方便、简单。例如,需要给新建的登录名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;
ALTER ROLE [View_Definiton] ADD MEMBER [kerry]
GO
相关文章
- SQL Server-聚焦在视图和UDF中使用SCHEMABINDING(二十六)
- SQL Server 2008管理工具出现 远程过程调用失败0x800706be解决方法
- 解决开启SQL Server sql Always on Group 事务日志增大的问题
- What “Clustered Index Scan (Clustered)” means on SQL Server execution plan?
- How do I UPDATE from a SELECT in SQL Server?
- C#操作SQL Server通用类
- com.microsoft.sqlserver.jdbc.SQLServerException: 对象名 ‘DUAL‘ 无效 | Druid双数据源MySQL+SQL server
- sql server 小技巧(4) Sql server 排序时让空值排在最后
- SQL Server: Get table primary key and Foreign Key using sql query
- sql server 数据分析优化实战(一)——SQL语句优化
- Sql Server 优化 SQL 查询:如何写出高性能SQL语句
- SQL Server索引进阶第五篇:索引包含列 .
- SQL Server Transaction Log Truncate && Shrink
- 针对【create-nuxt-app新版本v3.2.0】构建项目时没有server配置以及运行后弹出收集匿名数据选项等问题的解决方法
- JavaWeb项目实现SQL Server的增删改查登录(大作业)
- SQL Server SSPI handshake failed报错,sql server修改windows账户密码的影响,sql server订阅发布客户端访问一直报错
- sql server段落还原/部分还原,sql server文件组的备份还原,sql server文件的备份还原
- sql server 分布式查询不支持 Xm l数据类型。远程对象 xxxxx 包含 xml 列
- (1.2)sql server for linux 开启代理服务(SQL AGENT),使用T-SQL新建作业
- sql server代理服务无法启动(SQL Agent):OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason: 2).
- 最小配置启动SQL SERVER,更改SQL Server最大内存大小导致不能启动的解决方法
- (4.9)SQL Server如何校验备份文件
- Sql Server 取xml中节点值
- SQL Server Profiler 跟踪某个数据库某张表sql语句
- SQL Server中的索引结构与疑惑
- 关于SQL Server服务占用内存过大---限制数据库内存使用
- sql server登录名、服务器角色、数据库用户、数据库角色、架构区别联系
- SQL Server 单表数据备份和恢复
- SQL Server中用Case When Then多条件判断sql
- Windows2003操作系统SQL Server 2008安装图解(详细)