How to Throttle Logins to SQL Server
So, uh, you can use WAITFOR in a logon trigger:
|
1 2 3 4 5 6 |
CREATE OR ALTER TRIGGER SorryNorm ON ALL SERVER FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'NormTheNewGuy' WAITFOR DELAY '00:00:15'; END; GO |
You probably don’t want it to be TOO long, lest their app report it as a connection timeout. You want it just slow enough to make them angry.
I mean, theoretically. I would certainly never do anything like this. And I’m certainly not as diabolical as Dave Dustin:
https://twitter.com/venzann/status/959509799401529344
And as long as I’m talking about triggers, Marc Brooks had a request:
Sure! Here you go:
|
1 2 3 4 5 6 7 8 9 |
USE master; GO CREATE TRIGGER [I Can't Go For That] ON DATABASE AFTER CREATE_TABLE AS BEGIN ROLLBACK END GO |
That one’s an especially bad idea because it’ll likely break 3rd party apps and maybe even SQL Server upgrades.
And I know what you’re thinking: does that trick work in TempDB? Kinda – it prevents the creation of “regular” tables, like dbo.Whatever, but it doesn’t prevent the creation of temp tables.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Haha.. Nice fun with trigger.. 🙂
Thanks a lot for sharing..!
For additional fun add some randomness to the delay amount.
Robert – yeah, that’s in the post.
My favorite is the trigger I wrote that randomly prevents DDL commands. Along with a fun message.
https://sqlstudies.com/2014/04/08/t-sql-tuesday-53-why-so-serious/
Kenneth – HAHAHA, wow, I love the combination of the random number and the message. Nicely done.
I found a practical use for this:
CREATE OR ALTER TRIGGER VendorWatcher ON ALL SERVER FOR LOGON AS
BEGIN
IF ORIGINAL_LOGIN()= ‘EandCService’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘NoReply’,
@recipients = ‘nunya@business.org’,
@subject = ‘The vendor is up to no good again.’ ,
@execute_query_database = ‘Master’,
@body = ‘Call them now.’,
@body_format = ‘html’
END;
GO
HAHAHA