Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using a user which has read permission only. It basically has permission to

Connect

Execute

However, it does not have execute permissions on Functions and Stored Procedures. In part of my reports, I am calling my own functions. I would like to give my readonlyuser permission to execute SP, Functions and Views.

I came across this code which shows how to give execute permission on Stored Procedures and Functions for a particular user.

Now, here it looks like this permission applied to existing SP and functions only. If you add a new function, you have to run this code again to grant permission.

Questions

If my above interpretation is not wrong, is there a way to give execute permission to a read only user so that you have permission on all functions and stored procedures, even if you create new functions?

Can I apply the same permissions on Views?

In this thread, it is said you cannot give execute permission on function which returns a table. Is that true?

I know there are a couple of questions but all of them are related. Better to be in one place.

It is curious how in your script you are worrying about the schema for the grant, but you assume everything is in the same schema for the OBJECTPROPERTY check. I'd be careful about that.
–
Aaron Bertrand♦Apr 26 '12 at 17:54

+1 This is a good suggestion, but as I mentioned in my answer, you'd also have to grant SELECT on the schema in order to cover TVFs. This may be okay for the OP, but it should be noted for all readers that this permission will also apply to tables, views etc. in that schema.
–
Aaron Bertrand♦Apr 26 '12 at 18:00

Now you can grant EXEC on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.

Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE, CREATE_FUNCTION and CREATE_VIEW events, and grants permissions to a user (or a set of users, if you want to store them in a table):

The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.

EDIT

Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

Now if you create the following procedure, testuser will be able to execute:

I am a little bit confused. So your first solution does not use schema. It merely creates Grant statements for SP, functions and Views? In my case, I am not using table values function but could be in future. Just wanted to confirm. BTW My database comes from a third party. I don't know if they use schema, I not familiar with schemas at all.
–
JackofallApr 26 '12 at 18:35

1

My first code sample absolutely does use schema. dbo is a schema. It was just a quick example for a single table-valued function (which you asked about explicitly).
–
Aaron Bertrand♦Apr 26 '12 at 18:39

IC. Can you add to that code so I can get permission to execute SP, functions and View via schema? This was actually my main question but I also wanted to confirm about table valued function.
–
JackofallApr 26 '12 at 19:05