当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Quick way to grant Exec permissions to DB role for many stored procs

问题描述:

Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to n stored procedures.

When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role.

SQL Management Studio http://i26.tinypic.com/2r5g6c3.png

Here are the steps to apply permissions:

  • select the object that you want to grant/deny permissions in the list of Securables.
  • navigate to the list of Explicit Permissions below.
  • select the Grant or Deny checkbox as appropriate.

Repeat the above for n objects. Fire up some music to keep yourself entertained while doing this for 100+ objects! There's got to be a better way! It's a clickfest of major proportions.

Question:

Is there a faster way to perform this task using SQL Server Management Studio 2005? Perhaps another GUI tool (preferably free)?

Any suggestions for creating T-SQL scripts to automatically perform this task? i.e. create a table of all stored procedure names, loop, and apply the exec permissions?

网友答案:

This should do it:

CREATE PROC SProcs_GrantExecute( 
    @To AS NVARCHAR(255)
    , @NameLike AS NVARCHAR(MAX)
    , @SchemaLike as NVARCHAR(MAX) = N'dbo'
    ) AS
/*
 Proc to Authorize a role for a whole bunch of SProcs at once
*/
DECLARE @sql as NVARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + '
 GRANT EXECUTE ON OBJECT::['+ROUTINE_SCHEMA+'].['+ROUTINE_NAME+'] TO '[email protected]+';'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE @NameLike
 AND ROUTINE_SCHEMA LIKE @SchemaLike

PRINT @sql
EXEC(@sql)

This is Injectable as heck, so keep it for Admin use only.


I just want to add that Remus's suggestion of using schemas is the preferred approach, where that is workable.

网友答案:
USE database_name;
GRANT EXECUTE TO [security_account];

Don't forget the brackets :)

网友答案:

you can do this, however I'm not entirely sure how secure this is.

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
网友答案:

Easiest way is to:

GRANT EXECUTE ON myproc TO x

where x =

  1. SQL User
  2. Role
  3. AD Group/Account
网友答案:

Simply update the dbo schema and set add an EXECUTE permission on this schema to the desired user/role.

分享给朋友:
您可能感兴趣的文章:
随机阅读: