允许访问数百个SPs?

In Sql Server 2000/2005, I have a few NT user groups that need to be granted access to hundreds of stored procedures.

在Sql Server 2000/2005中，我有一些NT用户组需要被授予访问数百个存储过程的权限。

Is there a nice easy way to do that?

有什么简单的方法吗?

2 个解决方案

#1

Here's a script that I use for granting permissions to lots of procedures:

这里有一个脚本，我用来授予许多程序的权限:

DECLARE @DB  sysname ; set @DB = DB_NAME()
DECLARE @U  sysname ; set @U = QUOTENAME('UserID')

DECLARE @ID           integer,
        @LAST_ID     integer,
        @NAME        varchar(1000),
        @SQL         varchar(4000)

SET @LAST_ID = 0

WHILE @LAST_ID IS NOT NULL
BEGIN
    SELECT @ID = MIN(id)
    FROM dbo.sysobjects
    WHERE id > @LAST_ID  AND type = 'P' AND category = 0

    SET @LAST_ID = @ID

    -- We have a record so go get the name
    IF @ID IS NOT NULL
    BEGIN
        SELECT @NAME = name
        FROM dbo.sysobjects
        WHERE id = @ID

        -- Build the DCL to do the GRANT
        SET @SQL = 'GRANT EXECUTE ON ' + @NAME + ' TO ' + @U

        -- Run the SQL Statement you just generated
        EXEC master.dbo.xp_execresultset @SQL, @DB

    END   
END

You can modify the select to get to a more specific group of stored procs.

您可以修改select来获得更特定的存储proc组。

#2

Create a role in sql server.
在sql server中创建一个角色。
Write a script that grants that role permission to use those sprocs.
编写一个脚本，授予角色使用sproc的权限。
Add those NT user groups to that role.
将这些NT用户组添加到该角色。

#1