locked
Execute Permissions RRS feed

  • Question

  • Hi Team,

    one of user had db_reader,write permissions and excute permission also.

    in this showing we have right click that user properities.

    but he was not able execcute on  storedprocedure. How can i check that user

    and How can i give execute permission for that sp for temp solution for this.

    Tx


    subu

    Tuesday, April 17, 2012 2:43 PM

Answers

  • To find who has execute permission on a stored proc use

    SELECT HAS_PERMS_BY_NAME('usp_procName', 'OBJECT', 'EXECUTE');

    and to grant execute permission

    GRANT EXECUTE ON dbo.usp_ProcName TO DBUserName


    • Edited by Chirag Shah Tuesday, April 17, 2012 3:08 PM
    • Proposed as answer by SQLWork Tuesday, April 17, 2012 8:38 PM
    • Marked as answer by Iric Wen Thursday, April 26, 2012 2:00 AM
    Tuesday, April 17, 2012 3:08 PM

All replies

  • To find who has execute permission on a stored proc use

    SELECT HAS_PERMS_BY_NAME('usp_procName', 'OBJECT', 'EXECUTE');

    and to grant execute permission

    GRANT EXECUTE ON dbo.usp_ProcName TO DBUserName


    • Edited by Chirag Shah Tuesday, April 17, 2012 3:08 PM
    • Proposed as answer by SQLWork Tuesday, April 17, 2012 8:38 PM
    • Marked as answer by Iric Wen Thursday, April 26, 2012 2:00 AM
    Tuesday, April 17, 2012 3:08 PM
  • Hi Shah,

    Thanks for your reply.. sorry about my english.

    SELECT HAS_PERMS_BY_NAME('procname', 'object ', 'EXECUTE');

    i got out put : 1

    or i have to create a role and i have granth execute permission for this if it is correct please give the script for this .


    subu

    Tuesday, April 17, 2012 3:36 PM
  • Hi Shan

    as per ur suggestion i had give execute permission it is working fine

    tx


    subu

    Tuesday, April 17, 2012 5:55 PM