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)[1]',  'NVARCHAR(255)') != 'HAL0003'
    BEGIN
-- Make sure the command isn't touching DiscoveryOne in any way.
        IF EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','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.

Filed under: DBA Humor, Microsoft SQL Server, Security, SQLServerPedia Syndication, T-SQL Tagged: ddl triggers, Humor, microsoft sql server, security, T-SQL