SQL Server Performance

permission problem

Discussion in 'SQL Server 2005 General DBA Questions' started by khan, Jun 3, 2010.

  1. khan New Member

    Hi,

    I have the following scenario that i need help with

    I have a Login 'Smith' which is mapped to the user 'smith' and is member of the 'Public' role.

    and i have 4 tables in the DB TableA,TableB, TableC, TableD

    I want the login 'Smith' to be able to Ins/upd/Del/Sel on TableA but only Sel permission on All the rest of the Tables(TableB, TableC, TableD).

    So far i have done the following:

    GRANT SELECT,insert,update,delete ON TableA to Smith

    DENY update,delete,insert ON TableB to Smith
    DENY update,delete,insert ON TableC to Smith
    DENY update,delete,insert ON TableD to Smith

    GRANT SELECT ONON TableB to Smith
    GRANT SELECT ONON TableC to Smith
    GRANT SELECT ON ON TableD to Smith


    The problem:
    the above permission works fine as long as i type the t-sql

    when i tried to run a stored proc it asks for Execute permission for the user smith on the sp. After granting the exec permission on sp to the login 'Smith'.

    When i run the stored proc with logic to updates tableB,tableC or TableD
    it is still updating the DB which i don't want

    the question is how to prevent that i don't want to grant permission to every single SP and function .

    Thanks for a quick response.

    Regards,

    Ajjee

  2. FrankKalis Moderator

    Who owns that SP? A procedure typically runs in the security context of the owner of the procedure. If the owner has UPDATE permissions, so will the user executing the procedure.

Share This Page