Post reply

Table Permissions required when SP is run

  • May 7, 2002 at 7:14 am

    #78144

    Does anyone know why......:

    I have created tables owned by dbo.

    I have created SP's that are owned by dbo that perform selects on those tables.

    (The Sp's are basic selects statements)

    I have given exec permissions on the SP's to a role.

    When that SP is run (by a user in the role), the select permissions denied message appears. (The User is in no other roles).

    The only way to stop these is to grant select permissions on the tables to that role.

    The same occurs for inserts and updates.

    I have long existing databases where this has never been an issue (ie: as long as the user is in a role that has exec permissions on the Sps, and the SPs are owned by dbo along with the tables, then no permissions need be granted on the tables themselves), but on two recently created databases, this has happened, and permissions to the tables have to be given to the main role accessing them.

    Does anyone know what could be occurring?

    Many Thanks

  • May 7, 2002 at 9:00 am

    #428600

    You are not using dynamic SQL are you? That will always be the issue if you do.

    Otherwise can you post the ddl of the table and SP to see if we can find an issue (feel free to change item names if a security or developement issue)?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • May 7, 2002 at 9:55 am

    #428609

    Hi

    Thanks for the quick response.

    The SP does not contain Dynamic SQL.

    Following on from your comments, a bit more investigation revealed the issue, it was connected to Database ownership.

    The SP's are stored in one database

    The tables in another.

    I log into Enterprise Manager under my NT account.

    I created the new database. The database owner was therefore me, rather than sa which is what we use for all other databases.

    I believe this is what they call a broken ownership chain!!

    I have changed my log in into the enterprise manager to sa to avoid this in future, and changed the database owner using sp_changedbowner.

    The Sp's now run fine without the need to give the table permissions.

    Thanks for your help.

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

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