Post reply

How to deny write permission in database ( User has read-only permission on database in addition to execute permission on few SP's)

  • March 22, 2017 at 6:07 am

    #401824

    User has data_reader permission on the database. User has also execute permission on few SP's.
    Currently the SP's have only select statements but there are chances of modifications. I need to deny any insert statements being run inside the SP's.

    Grant execute permission seems to overrides deny/revoke insert permission on the object. Is there any workaround ?

    Thanks in advance !

    Sanz

  • March 22, 2017 at 6:42 am

    #1934284

    What you're looking at here is called ownership chaining. https://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

    If a user executes an SP which has an owner of dbo, which inserts data into a table who's owner is dbo, the insert will happen. It doesn't matter if the user has insert permissions or not, as the ownership is chained. this means you can have applications with only Execute permissions on a database (no read/write/etc), and function fine; as they select, insert, update and delete through the use of SPs.

    In your case, the simple answer would be to not allow the user to execute SPs that insert data into tables, or do anything else you don't want the user to do.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply