Can SQL Server 2008 / 2012 have execute permission in a function for a user, but not for a table used in the function?

Posted on

Question :

I have a function that returns a table. This function SELECTS DB1.dbo.table1

user1 has SELECT permission in this function only, but when selecting from the function error comes up stating that the security context does not allow access to DB1

Can a user have permissions to read only functions but not tables?

Answer :

In SQL Server you can define the execution context. This way the user only has rights to execute the function, but when it is executed, it runs in the context specified.

http://msdn.microsoft.com/en-us/library/ms188354(v=sql.100).aspx

Functions (except inline table-valued functions), Stored Procedures, and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | ‘user_name’ }

The following example would execute the procedure as the owner of the procedure, generally dbo, rather than the user calling it.

CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS OWNER
AS
SELECT user_name(); 
GO

Yes, you can grant SELECT permission only to the function without granting SELECT permission to the underlying table. User will be able to read the table data through ownership chaining (assuming they have the same owner). Make sure there is no conflicting permission of the user to the function (e.g. DENY SELECT on the table), as well as no conflicting EXECUTE AS (who has no permission to the table) on the calling procedure or within the function.

Leave a Reply

Your email address will not be published. Required fields are marked *