Home » Grant permissions on all functions and stored procedures

Grant permissions on all functions and stored procedures

by Vlad Drumea
0 comment

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.

That’s it, that’s the post.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.