SQL Server Performance

EXECEUTE AS CALLER

Discussion in 'ALL SQL SERVER QUESTIONS' started by Ramkumar Mu, Feb 28, 2013.

  1. Ramkumar Mu New Member

    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?
  2. Luis Martin Moderator

    Welcome to the forums.

    Yes.
  3. Ramkumar Mu New Member

    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
  4. Shehap MVP, MCTS, MCITP SQL Server

    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
  5. Ramkumar Mu New Member

    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.
  6. Shehap MVP, MCTS, MCITP SQL Server

    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
  7. Ramkumar Mu New Member

    The user is SQL Authenticated DB user
  8. Shehap MVP, MCTS, MCITP SQL Server

    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”
  9. Ramkumar Mu New Member

    looks weird for me too...
    So, I created this account on my server to test. I have attached the scripts above too...

Share This Page