This article applies to SQL Server 2008 R2 and earlier editions. It has not been verified with 2012

SQL Server ships with many different methods and tools for maintaining a high-level of security, from TDE to certificate-based authentication, to the use of Policy-Based Management (PBM) that we all know and love.  Auditing options, too, abound with options to capture live data for failed access attempts and connect various processes to react to unauthorised actions or access attempts.

This short article will talk about triggers in general, and their use in the context of security on SQL Server.  For those new to the topic, I'll talk first about what a trigger is, the three main types and their use.

Triggers are simply entities that fire a stored set of instructions when the conditions are met.  Essentially, they boil down to two parts - condition and action.  The conditions can range, from the arrival of a given date/time to the execution of an event or member of an event group (more on this below).  The action can be one or more actions, stored procedures or even calls to other triggers.  For the Windows Server sysadmins amongst you, this is comparative to creating a Task in Scheduled Tasks, where you set the conditions, actions and other parameters to do with the task, which is a self-contained 'unit'.

The three main types of trigger are: DML triggers, DDL triggers and LOGON triggers.  Each class of trigger has its own syntax and rules, and parameters affecting the outcome (although some are shared).  These are documented extensively in Books Online. The syntax and full BOL page is available here: http://msdn.microsoft.com/en-us/library/ms189799.aspx.

Let's look at an example. Imagine I own a coffee shop, with the Electronic Point of Sale application embedded in the till (or on a separate workstation) connected to a SQL Server Express backend.  Each transaction made gets placed into a table, whose definition is shown below:

CREATE TABLE dbo.salesTable 
(txID INT PRIMARY KEY, 
 product VARCHAR(MAX), 
 amount MONEY, 
 whenMade DATETIME, 
 whoBy VARCHAR(50))

Now I've just employed a new sales assistant who's handy with computers.  I don't want to run the risk of having money stolen.  There are numerous security measures I might put in place to prevent them - software measures (i.e. role-based access) and physical measures (CCTV), but as a backup measure I'd like SQL Server to tell me if the books are cooked.  The following trigger could be suitable:

EXEC sp_addMessage @msgnum=50009, @severity=10, 
@msgtext='Attempted data deletion from sales table detected.';
CREATE TRIGGER dbo.theftTrigger
ON dbo.salesTable
AFTER DELETE
AS
BEGIN
 ROLLBACK;
 DECLARE @whoDidIt VARCHAR(50);
 SET @whoDidIt = ORIGINAL_LOGIN() + ' is the culprit.';
 RAISERROR(50009, 10, 1) WITH LOG;
 RAISERROR(@whoDidIt, 10, 1) WITH LOG;
END

Now simply configure SQL Mail and an appropriate mail profile, then set an e-mail alert to send an e-mail to the predefined operator (owner) when the message number is equal to 50009.  The owner will then be notified by e-mail instantly if the delete attempt is ever made, and it will not be successful - the transaction will be rolled back.  (Note that you cannot use error code 50000, this is reserved for system use).

The next type of trigger is a DDL trigger. These are useful for responding to specific events, or event groups.  The example below sets up a trigger which activates whenever a new login credential is created.  It uses an event group called CREATE_LOGIN, which encapsulates several events (not to be confused with the CREATE LOGIN statement which will create a single server login) and activates whenever the statement is executed.

USE AdventureWorks
GO
CREATE TRIGGER dbo.loginTrigger
ON DATABASE
FOR CREATE_LOGIN AS
BEGIN
 IF ORIGINAL_LOGIN() <> 'sa'
  ROLLBACK;
  PRINT 'Creating logins is not allowed.  Please contact the SQL Server DBA for assistance.'
END

The above code is useful for preventing any non-DBA (in this case any user that is not 'sa') from creating new logins.  This helps ensure only authorised users have access to the database and can tie in nicely with a business process for creating new users.

A full list of DDL events from Microsoft is available here: http://msdn.microsoft.com/en-us/library/bb522542.aspx .

Triggers aren't limited to login-related events. You can also set up more complex triggers of all three types to maintain security. For example, imagine you have a small business and a SQL Server database with an employees table.  It's a single table, maintaining records for a few dozen employees, unnormalised.

CREATE TABLE dbo.employees 
( empNo INT PRIMARY KEY, 
  forename VARCHAR(50),
  surname VARCHAR(50), 
  dob SMALLDATETIME,
  jobTitle VARCHAR(50), 
  salary MONEY )

Now we have a malicious employee named Bob.  He is unhappy with his salary package, and happens to be the senior HR administrator with privileged access (db_owner) on the database.  The information from the table goes to a third-party payroll provider monthly.  What's to stop Bob from sending a quiet UPDATE statement?

UPDATE dbo.employees 
SET salary=50000 
WHERE forename='Bob' AND surname='Johnson';

This could be an effective countermeasure:

USE HR
GO
CREATE TRIGGER dbo.protectSalaries
ON dbo.employees
INSTEAD OF UPDATE AS
BEGIN
 IF ORIGINAL_NAME() = 'Bob'
  ROLLBACK;
  PRINT 'You are not authorised to update this table.  Your login has been disabled.'
  ALTER LOGIN 'Bob' DISABLE;
END

But what's to stop Bob from disabling the trigger in the first place?  For DML triggers, only the ALTER TABLE permission on the affected table is required.  Bob would have this if he was a db_owner.  A couple of solutions (not the only ones):

1. Give Bob public role permissions and grant specific permissions on the table not including ALTER TABLE.
2. Create the following trigger:

USE HR
GO
CREATE TRIGGER dbo.stopDroppingTriggers
ON DATABASE
FOR DROP_TRIGGER
BEGIN
 ROLLBACK;
 PRINT 'Dropping triggers is not allowed.'
END

(Note that to disable a DDL trigger with full server scope (ON ALL SERVER), the user must have CONTROL SERVER permission.  For DDL triggers with DATABASE scope, the user must have ALTER ANY DATABASE DDL TRIGGER permission).

The third type of trigger is a LOGON trigger, which is a specific class of trigger that allows execution of SPs or code on logon from any defined user.  This can be very useful for starting procedures on behalf of an application, for example, or delivering some information.  From a security perspective, it can be useful to ensure only a limited number of users, for example, are logged into the database at any one time (script below adapted from Microsoft example, http://msdn.microsoft.com/en-us/library/ms189799.aspx):

CREATE TRIGGER dbo.maxUsersTrigger
ON DATABASE
FOR LOGON AS
BEGIN
 IF (SELECT COUNT(*) 
   FROM sys.dm_exec_sessions 
   WHERE is_user_process = 1) > 10
  ROLLBACK;
END

This has the effect of blocking login for users.  Useful if for example only 10 people have access to the database and use it frequently.  Leaving out the argument about one person having more than one session open, this might supplement your security policy by ensuring an 11th 'outsider' cannot access the DB.  Or this might be put in place to ensure server resources remain consistent and high-quality for the users who are logged in - useful on a struggling server.

Triggers can also fire other triggers, which can fire other triggers.  This makes them both dangerous and useful.  See Microsoft's excellent article here about managing trigger security: http://msdn.microsoft.com/en-us/library/ms191134.aspx.

One more note to make. I have observed strange behaviour from SQL Server when using FOR LOGON triggers.  In the latest example, I created a simple table with one INT column, populated it with one data value (0) then wrote a FOR LOGON trigger to increment the data value by 1 every time the user 'sa' logged in.  The idea was to check, over a period, how often sa was used.  When I had finished with the trigger (it worked fine), instead of dropping the trigger I dropped the table first.

The strange behaviour that occurred was that suddenly I received the error message 'Login failed for user [me] due to trigger execution.'  Unfortunately this occurred for everyone trying to access the SQL server, across all databases. I resolved this by using the Dedicated Administrator Connection (DAC) through SQLCMD (use the -A flag).  The DAC doesn't fire logon triggers which meant I was able to query sys.system_triggers and DROP the offending trigger. Then e-mailed everyone offering my apologies for the 5-minute 'outage' :-S

This is a demonstration of how LOGON triggers in particular can go wrong. More information and useful links are available from the MSDN/TechNet Microsoft networks.