I am trying to assign execute permissions to users on a stored proc. The users do not have read permissions on the tables called inside the SP. When i read the MSDN site about EXECUTE AS CALLER option, it says... "...The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module." does this mean the users who are executing the procedure must have access to all the underlying tables inside the SP?
Thanks Luis. I did a check with sample table... I created a stored proc AccessTestTable that does a select to the table TestTable. I created a Testuser and granted exec permission to proc AccessTestTable. Even though the Testuser did not have select permission on the table (he could not do a select * from table outside the proc), he can access the table via the stored proc. Please explain me why is this so... Code samples... -- Create Scripts CREATE LOGINTestuserWITHPASSWORD='abcde#123' CREATE USERTestuserFROMLOGIN Testuser CREATEPROCEDURE AccessTestTable WITH EXECUTEAS CALLER AS SELECT SUSER_SNAME () SELECT TOP 10 *FROM TestTable GRANT EXECUTEONAccessTestTableto Testuser EXECUTE ASUSER= 'SQLUser1' SELECT *FROM TestTable -- This query throws error EXECUTE AccessTestTable -- This returns values from the table without error
That is exactly why luis has said to you should have access for all objects exists within SP otherwise you can try with exec as owner which should execute SP using the same privileges of SP owner which can be schema owner here Please let me know if any further help is needed
I am confused. In my example, the user does not have access to the objects, but executing the SP with his privileges (EXECUTE AS CALLER) and still, he can run the proc without errors. I see this as a conflict with what MSDN says.
Which user you are talking about ..? DB user ( SQL Server Login) or Application user ...? Please elaborate because if it is Application user , it will be authenticated to DB using the SQL login configured within Web config , .ini file or database connection manager in generic
Indeed it is weird , but you can check this SQL user is granted to access any proxy or any other elevated privileges are granted to it that permits for it to exec this SP with the option “Exec as Caller”
looks weird for me too... So, I created this account on my server to test. I have attached the scripts above too...