Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SQL Server Administration (2005)  Object Security Via Stored Procedure Issue

Author  Topic  nudais Starting Member 2 Posts Posted - 2011-04-23 : 17:01:44 According to the documentation, a user who is granted execute permission on a stored procedure automatically has the necessary permissions to access any tables that stored procedure touches, even though they do not have direct access to those tables. Fair enough. I have created 2 users, USERA and USERB. USERA owns a table (tab1) and a stored procedure (sp1) which inserts a row into tab1. Both tab1 and sp1 belong to schema1 owned by USERA. I granted execute permission on sp1 to USERB and set also USERB's default schema to schema1. As expected, USERB can execute sp1 and successfully inserts a row into tab1, even though USERB cannot even select from tab1 directly. Now the weird bit. As USERA, I revoked execute permission on SP1 from USERB. As sa/dbo, I changed the ownership of schema1 from USERA to USERB. So, USERB now owns schema1 which in turn owns tab1 and sp1. As expected, USERB can see tab1 and can execute sp1. As USERB, I granted execute permission on sp1 to USERA. BUT....when USERA executes sp1, I get an "INSERT permission denied" error on tab1. If USERA can execute sp1 (which it can), then it should be able to insert a row into tab1 (which it cannot!). Why? What am I missing? Thanks for any help setting me straight.