Preventing All Updates and Deletes

  • I have a need to prevent all updates and deletes from occuring in an database. Inserts and Selects are ok. What are the reccomendations for doing this? Can it be done via SQL Server Policy Management? I need to restrict everybody including sa accounts.

    Thanks for the help

    Steve

  • You can't directly restrict sa (sysadmin) accounts from any activity in SQL Server. You would have to use a trigger to prevent sa activity.

    For only non-sa, you could try:

    DENY DELETE ON SCHEMA::dbo TO public

    DENY UPDATE ON SCHEMA::dbo TO public

    -- add different/other schemas as needed

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher (10/14/2013)


    ...You would have to use a trigger to prevent sa activity....

    Every sa could disable the trigger and still perform updates.

    Whatever concept is applied, a system admin can change it (including policies). Even just for a moment...

    AFAIK, all you can do is to monitor the changes. But even those can be "influenced"...

    You're basically left with only one option: trust your admins and every db_owner. The remaining users can be handled as Scott described.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • LutzM (10/14/2013)


    ScottPletcher (10/14/2013)


    ...You would have to use a trigger to prevent sa activity....

    Every sa could disable the trigger and still perform updates.

    I should have mentioned that. I was thinking of something that might have some chance, such as an encrypted trigger with critical code in the trigger itself. But even then, as you've noted, a good sysadmin would find a way thru.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

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