如何对一个角色进行一次性的授权？

Question

我现在建了一个角色，想让它访问大多数表和所有视图及大多数存储过程。用GRANT ALL ON 表名（或视图名或存储过程名） TO 角色名 一个个来授权太麻烦了。请问有没有一次性授权的办法呢？我想在一次性授权后，再回收个别权限就行了。

Answer 1

--管好sql的用户,防止访问他不该访问的数据库(总控制,明细还可以控制他对于某个数据库的具体对象具有的权限)  
 
--切换到你新增的用户要控制的数据库  
use  你的库名  
go  
 
--新增用户  
exec  sp_addlogin  'test'            --添加登录  
exec  sp_grantdbaccess  N'test'            --使其成为当前数据库的合法用户  
exec  sp_addrolemember  N'db_owner',  N'test'            --授予对自己数据库的所有权限  
 
--这样创建的用户就只能访问自己的数据库,及数据库中包含了guest用户的公共表  
go  
 
--删除测试用户  
exec  sp_revokedbaccess  N'test'            --移除对数据库的访问权限  
exec  sp_droplogin  N'test'            --删除登录 
不知道有用不？

Answer 2

以下SQL批量产生授权的SQL语句,然后一起执行就可以了.select 'GRANT ALL ON '+name+' TO [角色名]' 
from sysobjects 
where xtype in ('U','P','V') and name not in ('[需回收权限的表,视图,存储过程]')

Answer 3

说明:
  1.xtype in ('U','P','V')是指所有的表,存储过程,视图.
  2.name not in.. 可排除掉需回收权限的对象.

Answer 4

方法不错。可是，那个查询语句的结果里有一些系统对象，如dt_isundersourcecontrol_u这样的，如何排除掉呢？

Answer 5

不了解楼主数据库里dt_isundersourcecontrol_u是什么对象?
想排除掉的话,可以灵活使用where后面的条件,例如:where xtype in ('U','P','V') 
and name not in ('[需回收权限的表,视图,存储过程]')
and name not like 'dt_%' --> 排除掉名称以'dt_'开头的对象.

Answer 6

ap0405140：
dt_isundersourcecontrol_u 这样的对象是系统自带的，我也不知道干什么用的。你说的方法我会用。不过不太好，还有其他这样的对象呢。

Answer 7

dt_isundersourcecontrol_u  
这是什么对象？之前已经制定了表，存储过程，视图3种

Answer 8

据我所知,没遇到过系统自带dt_isundersourcecontrol_u这种对象的.
如果要排除的对象很多,需授权的对象少于需回收权限的对象.那还是用name in好了.where xtype in ('U','P','V') 
and name in ('[需授权给角色的对象]')

Answer 9

复制-->新增一个查询连线-->粘贴-->全部选中(Ctrl+A)-->执行(F5)

Answer 10

那建议放存储过程里,相关参数(数据库名,角色名,排除的对象等)传入.
集中产生SQL语句的结果,然后用游标分句的执行.