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
You can create a role and then grant / revoke permissions to it. Any user that is a part of the role will inherit the permissions.
Below is an example to get you started :
-- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
---- Grant access rights to a specific schema in the databas
GRANT
SELECT
ON SCHEMA::dbo
TO TableSelector
GO
-- Add an existing user to the new role created
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO
-- Revoke access rights on a schema from a role
DENY ALTER -- you can customize here ...
ON SCHEMA::dbo
TO TableSelector
Best Answer
This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start:
After this, Bob will have wide read-only privileges in the database. He will be able to read from all tables, views on those tables, and in-line functions. He will not be able to execute any procedures or use non-inline functions, however.
You will need to grant specific permissions to the Readers role for safe functions and procedures you wish Readers to have access to. There may be steps you can take to make this process easier (such as grouping objects into a schema and granting execute permission on the schema instead of individual objects) but there are too many details to cover here.
One thing to be aware of is that the lack of data modification privileges would not prevent a Reader from changing data via a stored procedure she has been granted execute permission on, if the procedure and object being modified share a common owner. Nor would an explicit deny be respected in this case. This feature is known as Ownership Chaining.