Obviously you can block data modifications through permissions fairly easily. Blocking sysadmins and dbos is somewhat more problematic. One downside to using permissions to block data modifications is that the user attempting to modify the data receives a permissions error that doesn't give any explanation as to why data modifications are blocked. This post shows a possible different way to block data modifications that doesn't use permissions but rather uses a trigger that throws a custom error that helps explain why the modifications are blocked. The trigger also does a rollback to stop the data modification operation(s). The ultimate effect is that the data in the table is static for all users including sysadmins and dbos, plus any user attempting to modify the data within the table receives a useful error message explaining why data modifications are blocked. I also added into the custom error message sample below a possible solution for temporarily allowing data modifications against the table by temporarily disabling the trigger.
You can customize the error message as required. You can add and remove arguments as required also. See SQL BOL about the
RAISERROR command for more information.
This solution assumes you have a table named dbo.MyTable that you want the data to be static for (ie, block data modifications for).
After executing the code below and creating the trigger named tr_MyTable_StopDataMods, you can test by performing INSERT, UPDATE, and DELETE operations against MyTable. For each attempt at modifying the data within dbo.MyTable, you should see that all data modification operations are blocked and that you receive a useful error message. You can also temporarily disable/reenable the trigger through SSMS or Transact-SQL (see below) to test that functionality.
Please note that the ROLLBACK within the trigger is like a sledgehammer. If you included the attempted data modification(s) to dbo.MyTable in a larger transaction where you attempt to modify other the data of other tables, the trigger ROLLBACK forces the cancellation and ROLLBACK of all of your data modifications. Please keep that in mind.
Please share any ideas or feedback that you may have. Thanks!
Please see a variation of this post
here. Mladen Prajdic (
Blog|
Twitter) suggested I use an INSTEAD OF trigger and eliminate the ROLLBACK. Thanks for the suggestion Mladen!
CREATE TRIGGER dbo.tr_MyTable_StopDataMods
ON dbo.MyTable
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
ROLLBACK;
RAISERROR(
N'The data in table %s is static. All data modification operations are blocked. Disable the trigger %s to modify table data.', -- Message text.
14, -- Severity
1, -- State,
'dbo.MyTable', -- first argument (table name)
'dbo.tr_MyTable_StopDataMods' -- second argument (trigger name)
);
END
GO
-- Code to temporarily disable trigger
ALTER TABLE dbo.MyTable DISABLE TRIGGER tr_MyTable_StopDataMods;
GO
-- Code to reenable trigger
ALTER TABLE dbo.MyTable ENABLE TRIGGER tr_MyTable_StopDataMods;
GO
Code tested only on SQL Server 2008 Dev Ed.