SQL Server: Quick way to grant Exec permissions to DB role for many stored procs

Answer 1

Easiest way is to:

GRANT EXECUTE ON myproc TO x

where x = 1) SQL User 2) Role 3) AD Group/Account

Answer 2

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 '+@To+';'
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.

Answer 3

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