November 23, 2016 by Kenneth Fisher
I while back as part of a T-SQL Tuesday post I created HAL0001, then later HAL0002 with a long-term goal of building up a database version of HAL9000. Well, at the rate I’m going I may have to pass this on to my kids (or even grandkids). But in the mean time here is HAL0003.
- HAL0001 randomly stopped you from making DDL changes.
- HAL0002 stopped you from using NOLOCK in code.
- HAL0003 will not let you touch a given table (DiscoveryOne as it happens) and will disable your login and kill your connection if you try.
Just as a side note: Please, please, please! Never put any of these in production. They are meant as a joke only.
-- Create a database to put SPs that use EXECUTE AS OWNER and TRUSTWORTHY -- https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/ CREATE DATABASE SPs; GO -- Make it trustworthy ALTER DATABASE SPs SET TRUSTWORTHY ON; GO USE SPs; GO -- Change the owner to a sysadmin (sa was just convenient) EXEC sp_changedbowner 'sa'; GO CREATE PROCEDURE KillUserAndConnection WITH EXECUTE AS OWNER AS BEGIN DECLARE @sql nvarchar(2000); DECLARE @error nvarchar(2000); -- Disable the current user SET @sql = N'ALTER LOGIN ' + QUOTENAME(ORIGINAL_LOGIN()) + N' DISABLE'; -- Raise a sever error. This will disconnect the current connection. SET @error = N'I''m sorry Dave. ' + ORIGINAL_LOGIN() + ' misbehaved. I had to get rid of them.' ; EXEC sp_executesql @sql; RAISERROR (@error, 25, 1) WITH LOG; END GO -- Make it so anyone can connect to this DB and run this SP. GRANT EXECUTE ON KillUserAndConnection TO public; GRANT CONNECT TO public; GO USE Test; GO -- Create the all important table DiscoveryOne CREATE TABLE DiscoveryOne ( Id INT NOT NULL IDENTITY(1,1), Memory BIGINT NOT NULL, CPUCount BIGINT NOT NULL, DiskSpaceInMB BIGINT NOT NULL ); GO -- Here it is! HAL0003! CREATE TRIGGER HAL0003 ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_FUNCTION, ALTER_FUNCTION, CREATE_VIEW, ALTER_VIEW, CREATE_TRIGGER, ALTER_TRIGGER, CREATE_TABLE, ALTER_TABLE AS -- Make sure that this doesn't affect itself IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)', 'NVARCHAR(255)') != 'HAL0003' BEGIN -- Make sure the command isn't touching DiscoveryOne in any way. IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','nvarchar(max)') LIKE '%DiscoveryOne%' BEGIN -- If it did, get rid of them. With extreem prediduce. EXEC SPs.dbo.KillUserAndConnection; END END GO
Now let’s make sure that HAL0003 works correctly!
-- Create a login and give it ddladmin permissions on the DB where DiscoveryOne exists. CREATE LOGIN SQLTest WITH PASSWORD = 'SQLTest'; GO USE Test; GO CREATE USER SQLTest FROM LOGIN SQLTest; GO EXEC sp_addrolemember 'db_ddladmin','SQLTest'; GO
And in another connection log in as SQLTest and run this:
USE Test; GO ALTER TABLE DiscoveryOne ADD Test INT; GO
And one last time. The HALs are meant as a joke. Some of the code in them is interesting and could make a good template for something else, but I wouldn’t ever put them in production as is.