October 14, 2013 at 2:26 pm
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
October 14, 2013 at 2:59 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2013 at 3:14 pm
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.
October 15, 2013 at 8:02 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply