There’s probably a gazillion stored procedures out on the internet to grant permissions, and I needed one, so I cruised over to this site and found one. A couple of exceptions here to what I wanted to do, so I made a few changes to his base and created a new one. Here are the changes that I made:
- Added a cursor for ease of use, and got rid of the temp table
- Joined to sys.schemas to get the schema, and added the Schema, since we have so many schemas here at my shop
- Identified the TF and FN objects, and CASEd the appropriate permission type (EXEC or SELECT) for each
- Added QUOTENAME so that it would work properly for our users
IF object_id('usp_sql_grant_execute_permission') IS NOT NULL
DROP PROCEDURE [usp_sql_grant_execute_permission]
GO
CREATE PROCEDURE [dbo].[usp_sql_grant_execute_permission]
@login VARCHAR(50),
@RunEXEC BIT = 1 --1 will run the statements, 0 = Prints them
AS
SET NOCOUNT ON
DECLARE @procname VARCHAR(150),
@schema VARCHAR(10),
@xtype VARCHAR (10),
@sql VARCHAR(255)
-- Populate the temporary table with user defined stored procs and functions
DECLARE CS CURSOR
FOR
SELECT s.[name], x.[name], s.xtype
FROM sys.sysobjects s
INNER JOIN sys.schemas x
ON s.uid = x.schema_id
WHERE (xtype = 'P') OR (xtype = 'FN' OR xtype = 'TF')
OPEN CS
FETCH NEXT FROM cs INTO @procname, @schema, @xtype
-- Iterate through each of the rows on the table
WHILE @@fetch_status=0
BEGIN
-- Prepare and execute SQL statement
SET @sql = 'GRANT ' + CASE WHEN @xtype != 'TF' THEN 'EXEC ' ELSE 'SELECT ' END +'ON '
+ @schema + '.' + @procname + ' TO ' + QUOTENAME(@login)
-- do we run or print them out
IF @RunEXEC = 1
EXEC (@sql)
ELSE
PRINT @SQL
FETCH NEXT FROM cs INTO @procname, @schema, @xtype
END
CLOSE cs
DEALLOCATE cs
GO
I used to do a lot of administration, then didn’t do any for 100 years, and now I’m back doing some again. It’s fun for sure. Thanks to blog.strictly-software.com for contributing this code – we appreciate you!
Lee
----------------------------
http://blog.strictly-software.com/2010/01/grant-execute-permission-to-all-stored.html
2d2e546a-d8f5-47e4-8363-1a8b34fac122|0|.0