Granting EXEC permissions to stored procedures (SQL Server)
Granting execute permissions to all stored procedures in your database.
1: DECLARE @T TABLE(RowID INT Identity( 1,1 ), Permission varchar(200))
2:
3: INSERT @T(Permission)
4: SELECT CASE ISNULL(DATA_TYPE,'') WHEN 'TABLE' THEN ' grant select on ' ELSE 'grant exec on ' END + QUOTENAME(ROUTINE_SCHEMA) + '.' +
5: QUOTENAME(ROUTINE_NAME) + ' TO [username]'
6: FROM INFORMATION_SCHEMA.ROUTINES
7: WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
8:
9: DECLARE @text varchar(2000), @index int, @count int
10: SELECT @index = 1, @count = max(RowID) FROM @T
11: WHILE @index <= @count
12: BEGIN
13: SELECT @text = Permission FROM @T WHERE RowID = @index
14: exec (@text)
15: SET @index = @index + 1
16: END
Not that I would always recommend such a deed, however, if you are deploying your stored procedures to an external server, and should a need arise to grant permissions, there is always this code.
Knock yourself out if you want to encapsulate the above code as a stored procedure, and go ahead and create a web-service that would remotely grant execute permissions to your procedures.
Happy coding/hacking.