SQL Server Performance

Pemission Issue

Discussion in 'Getting Started' started by ehsan, Apr 18, 2007.

  1. ehsan New Member


    I need your help. I have created a Role "DeveloperX" I have granted "Select" and "Execute" permission.

    Now Some of the stored procedures are inserting, updating and deleting data. Please tell me how to restrict this role to Select and Execute. This role should not be deleting, updating and inserting data through stored procedure even this role has execute permission.


  2. MohammedU New Member

    When you granted execute permissions to a procedure, if the procedure is inserting/updating/deleting then role will get automatically insert/update/delete permissions...

    You don't need to grant execlusive insert/delete/update permissions...

    When you don't want the role have insert update delte permissions then why you are granting execute permission?
    Remove the execute permission and deny insert, update, delete....


    All postings are provided “AS IS” with no warranties for accuracy.
  3. ehsan New Member

    Thanks Mohammad.

  4. satya Moderator

    I believe it works bit different as compared to SQL 2000 and 2005, as you can control such access using user schemas. It would be better if you can confirm that bit of the question.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. ehsan New Member


    I am using SQL Server 2005. I know it works if I set permissions on Schemas. I was looking for to set permission on database level.

    Thanks Satya.


  6. satya Moderator

Share This Page