This is a brief post containing a script to grant permissions on functions and stored procedures to a database user or role in SQL Server.
Every few months I end up having to write variations of this piece of T-SQL, so I’m posting it here to save time next time I might need it.
Overview
The script is fairly simple, it requires one input for the @DbUserOrRole variable and goes through the non-system table-valued and scalar functions, as well as stored procedures, then builds the dynamic T-SQL necessary to grant the relevant permissions (EXECUTE for scalar functions and stored procedures, and SELECT for TVFs) and executes it.
All without needing a cursor 🙂 .
This has to be repeated whenever a new function or procedure is added.
For stored procedures, a future-proof solution is to just run GRANT EXECUTE TO [RoleOrUserNameHere];, but this doesn’t do anything for functions.
The script
Change the placeholder value for @DbUserOrRole and then execute it to grant permissions on the 3 types of objects.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | DECLARE @SQL NVARCHAR(MAX), @LineFeed NVARCHAR(5), @DbUserOrRole NVARCHAR(128); SET @LineFeed = CHAR(13) + CHAR(10); SET @SQL = N''; /*Specify the user or role name*/ SET @DbUserOrRole = N'UserOrRoleNameGoesHere'; /* build the dynamic T-SQL */ SELECT @SQL += N'GRANT ' + CASE WHEN [type] IN ('FN', 'P') THEN N'EXECUTE' WHEN [type] = 'TF' THEN N'SELECT' END + N' ON [' + CAST(OBJECT_SCHEMA_NAME([object_id]) AS NVARCHAR(128)) + N'].[' + [name] + N'] TO [' + @DbUserOrRole + N'];' + @LineFeed FROM sys.objects WHERE [type] IN ( 'FN', 'TF', 'P' ) AND is_ms_shipped = 0; /*Execute or print the generated T-SQL*/ --PRINT @SQL; EXEC (@SQL); |
That’s it, that’s the post.