Big Noise
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:
- CONTEXT_INFO
- sp_getapplock
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;
Going Further
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. You can also get a quick, low cost health check with no phone time required.
