SQL Server trigger security - granting privileges you are not authorized to

---

Download xSQL Compare Bundle, starting from $0!

---

Both DML and DDL triggers execute under the context of the user that caused the trigger to fire – in other words, if for example we have a DML trigger that fires whenever a row is deleted from table T then the trigger will fire under the context of the user that executes the delete statement. Does this tell you anything about the potential inherent risk with triggers? Unlikely, until you consider this: a rogue developer writes a DDL trigger that looks something like this:

CREATE TRIGGER DDL_RogueDev
    ON DATABASE
      FOR ALTER_TABLE
      AS
        GRANT CONTROL SERVER TO RogueDev ;
 GO

Now, if the RogueDev tries to get the trigger fired so that he can get Server Control his attempt will fail since he does not have permission to grant himself server control, but, remember what we said above! What is going to happen when the DBA with full control goes and alters a table in the database? You guessed it – he inadvertently will be granting server control to RogueDev!  Ooops!

There are trigger security best practices that the DBA can follow like maintaining a strict inventory of DML and DDL triggers in the database and on the server instance, disabling triggers etc.  However, a proactive DBA can do more – he can use the very triggers to protect his servers / databases against people like RougeDev. How? Here is one simple example – a DBA could write a trigger that looks something like this:

CREATE TRIGGER no_grant_server
    ON ALL SERVER
      FOR GRANT_SERVER
      AS
          PRINT 'What do you think you are doing!'
          ROLLBACK;
GO

What does this do? Anytime the DDL_RogueDev fires and attempts to grant server control to RougeDev the no_grant_server trigger will fire and prevent that from happening no matter under what authority the DDL_RogueDev may be running. Instead of printing a silly message you could log the attempt, send notifications etc.

Another quick and easy measure would be to utilize a tool like SQL Compare for SQL Server to take regular snapshots of the schema and compare those snapshots with each other and with the live database to determine what database objects may have been added or altered.

Such security countermeasures are not hard to implement but not many DBAs do, until they have been burned.