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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.

SELECT user_name(); 
share|improve this answer
except inline table-valued functions - is key Thank you – cusco Apr 19 '13 at 16:49

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

Your Answer


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.