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.