ms sql server 2000 GRANT EXECUTE on database to Roles

   今天想把当前数据库所有执行存储的权限给一个角色 ,于是写

GRANT EXECUTE on D TO PPRoles

结果是有语法错误，

开联机丛书，

貌似只能on单个存储过程

statement

是被授予权限的语句。语句列表可以包括： 

CREATE DATABASE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG 

好像没有指定库的Execute.于是。。。

sql 2005 可以实现 晕。。

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

sql 2000 实现方法 。。。。拼sql字符串

(1)

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO <insert_username>' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0	

(2)

declare @sql nvarchar(4000)
declare @db  sysname ; set @db = DB_NAME()
declare @u   sysname ; set @u = QUOTENAME('<insert_username>')
set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' + 
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'
exec master.dbo.xp_execresultset @sql,@db

[url:http://www.sqldbatips.com/showarticle.asp?ID=8]