Post reply

Permission denied on tables used within table-valued function

  • March 9, 2011 at 4:47 am

    #148606

    This is my first post on this forum so please be gentle with me!

    I have created a new User and role with very limited access to a database.

    It has Select permission on a table-valued function, but when I call the function from the User account

    it returns an error as it does not have Select permission on the tables used within the function

    I would rather not grant select permission to the user on all the required tables

    Function is within schema AMPROC, owner of schema is AMPROC.

    All tables are within schema dbo, owner of schema is dbo.

    The owner of the role is dbo.

    Error is as follows 'The SELECT permission was denied on the object 'TableName', database 'DatabaseName', schema 'dbo'.'

    is it right that even though the user has select permission on the function, they also require select permission on all tables used within the function? I've trawled the internet for information on this but can't find any answers so wondered if you guys could shed any light on the problem?

    thanks

  • March 11, 2011 at 4:20 am

    #1297521

    Just thought I'd post an update on this:

    I have managed to get around the problem by altering the function to add 'WITH EXECUTE AS <User Name>' to execute the function with higher privileges.

    not ideal as managing this through a code change rather than the security functionality but it is potentially a way round it

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply