Take the 2-minute tour ×
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.

- Grant Execute on all functions for testuser
declare @username varchar(255)  
set @username = 'testuser'  
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +  
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES  
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0   
and ROUTINE_TYPE='PROCEDURE'  

and for functions

declare @username varchar(255)  
set @username = 'testuser'  
SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +  
QUOTENAME(ROUTINE_NAME) + ' TO ' + @username FROM INFORMATION_SCHEMA.ROUTINES  
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0   
and ROUTINE_TYPE='FUNCTION'  

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

  1. 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?

  2. Can I apply the same permissions on Views?

  3. 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.

share|improve this question
    
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
add comment

2 Answers

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY):

DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE 
    WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
      OR type_desc = 'VIEW'
    THEN ' SELECT ' ELSE ' EXEC ' END 
    + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id])) 
    + '.' + QUOTENAME(name) 
    + ' TO ' + @username + ';'
 FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
( 
    type_desc LIKE '%PROCEDURE' 
    OR type_desc LIKE '%FUNCTION'
    OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

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):

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
    ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql       NVARCHAR(MAX),
        @EventData XML = EVENTDATA();

    ;WITH x ( sch, obj ) 
    AS
    (
        SELECT
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
    )
    SELECT @sql = N'GRANT ' + CASE 
      WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION' 
        OR o.type_desc = 'VIEW'
      THEN ' SELECT ' 
      ELSE ' EXEC ' END 
        + ' ON ' + QUOTENAME(x.sch) 
        + '.' + QUOTENAME(x.obj) 
        + ' TO testuser;' -- hard-code this, use a variable, or store in a table
    FROM x
    INNER JOIN sys.objects AS o
    ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

    EXEC sp_executesql @sql;
END
GO

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:

CREATE PROCEDURE foo.proc1
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT 1;
END
GO
share|improve this answer
    
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. –  Jackofall Apr 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. –  Jackofall Apr 26 '12 at 19:05
add comment

Can you make use of a schema and then grant permission to execute items in the schema to the user? More information regarding schema can be found here: http://msdn.microsoft.com/en-us/library/ms187940.aspx

share|improve this answer
2  
+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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.