Preventing All Updates and Deletes

Question

Preventing All Updates and Deletes

SW_Lindsay

SSCommitted

Points: 1983
More actions
October 14, 2013 at 2:26 pm

#299646

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

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

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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".

LutzM SSC Guru Points: 107049 More actions · Answer 2

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]

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

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".