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:
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.
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 =
Simply update the dbo schema and set add an EXECUTE permission on this schema to the desired user/role.