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

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
    
except inline table-valued functions - is key Thank you –  cusco Apr 19 '13 at 16:49
add comment

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.

share|improve this answer
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.