I was helping a client with an issue recently where they wanted to allow certain admin users to override changes currently in a table, but not allow anyone else to make changes.
The thing is, users had to be allowed to make other changes to the table, so it wasn’t something that could be handled easily with security features.
The example I’m going to show here is simplified a bit to get the code across to you, so keep that in mind.
Now, at first I was thinking about using some fancier features that SQL Server has to offer:
But after talking over with the Most Valuable Kiwi, those ideas quickly lost their luster. They would both require additional handling in case of errors, and sp_getapplock could have issues under concurrency.
What turned out to be the best idea was actually the simplest: create a temp table with a very random name (in this case, running SELECT NEWID() and removing the dashes from the result) and having the trigger look for that to decide whether or not it should allow the update.
See below for the demo code.
Thanks for reading!
USE Crap; GO /*Out ye devil*/ DROP TABLE IF EXISTS dbo.the_table; /*Okay you can come back*/ CREATE TABLE dbo.the_table ( id int PRIMARY KEY IDENTITY, dt datetime NOT NULL DEFAULT SYSDATETIME() ); GO /*One row is fine.*/ INSERT dbo.the_table ( dt ) VALUES ( DEFAULT ); GO /*Check me out*/ SELECT tt.* FROM dbo.the_table AS tt; GO /* The trigger, and why: Other options like SESSION_CONTEXT and sp_getapplock require a whole bunch of other handling and can be weird with connection pooling and under high concurrency * Clearing out session context when done * Error handling for sp_get applock to release locks * A whole list of other stuff! */ CREATE OR ALTER TRIGGER dbo.the_trigger ON dbo.the_table INSTEAD OF UPDATE AS BEGIN IF ( ROWCOUNT_BIG() = 0 OR OBJECT_ID(N'tempdb..#B7E66DC66D9C4C4182ECCF583D126677') IS NULL ) BEGIN RAISERROR('Only admins can update that column!', 11, 1) WITH NOWAIT; RETURN; END; ELSE BEGIN SET NOCOUNT ON; IF UPDATE(dt) BEGIN UPDATE t SET t.dt = i.dt FROM dbo.the_table AS t JOIN Inserted AS i ON i.id = t.id; END END; END; GO /* Create a #temp table for the trigger to pick up on */ CREATE OR ALTER PROCEDURE dbo.the_admin_procedure ( @id int, @dt datetime ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; CREATE TABLE #B7E66DC66D9C4C4182ECCF583D126677 ( the_crumb bit ); UPDATE t SET t.dt = @dt FROM dbo.the_table AS t WHERE t.id = @id; END; GO /* Non-admin users don't get the temp table */ CREATE OR ALTER PROCEDURE dbo.the_normal_procedure ( @id int, @dt datetime ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; UPDATE t SET t.dt = @dt FROM dbo.the_table AS t WHERE t.id = @id; END; GO /* In real life, you might be able to figure it out based on login names */ CREATE OR ALTER PROCEDURE dbo.the_real_procedure ( @id int, @dt datetime ) AS BEGIN SET NOCOUNT, XACT_ABORT ON; IF SUSER_SNAME() = 'sister_minnie' BEGIN CREATE TABLE #B7E66DC66D9C4C4182ECCF583D126677 ( the_crumb bit ); END; UPDATE t SET t.dt = @dt FROM dbo.the_table AS t WHERE t.id = @id; END; GO /* Show and tell */ EXEC dbo.the_normal_procedure @id = 1, @dt = '19000101'; EXEC dbo.the_admin_procedure @id = 1, @dt = '99991231'; SELECT tt.* FROM dbo.the_table AS tt;
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.