I know what you're thinking. "Did it fire six times or only five?" Well to tell you the truth in all this excitement I kinda lost track myself. You've gotta ask yourself one question: "Do I feel lucky?" Well, do ya, punk?
Introduction
Who is logging on to your SQL Server? Who is making production changes? Ground, please open up and swallow me whole, I dropped live.
These are some of the many questions and issues that confront every DBA daily. This article details the approach of using triggers to help with these tasks.
There are alternative approaches to these tasks. Extended events and SQL Trace are examples of alternative technologies, but hey, this article is called “Trigger Happy” so we are talking about triggers here. Adding triggers to your tables does not come for free. You are adding overhead to your system as each triggering action now has to perform extra work. Where the triggers write or update a table, you could face latch or lock contention.
Is this a problem for you? I have no idea of the answer to that question, you would need to ask yourself if your users would be adversely affected by adding such triggers. If that answer to that is yes, then don’t deploy and find an alternative solution. There is a special risk with logon triggers where you could potentially stop all SQL Server connections. This is certainly one way of fixing performance issues, but not an ideal goal if you value your job.
DDL Audit
How often do you make production DDL changes? Probably not too often is the answer. When DDL changes do happen, it is very useful to have an audit trail to clearly identify who did what and when. This allows you to fix your process in the event of an erroneous deployment making it to live. Maybe someone made a live change when you had no idea they had permissions to do so. Another scenario might be a third party app makes some automated DDL changes? In any event having that audit trail can be very useful for troubleshooting.
The following script will create an audit table for DDL changes in the master database and a trigger to insert to it: -
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON;
-- --------------------------------------------------------------------------------------------------------
-- DROP OBJECTS IF THEY EXIST
-- --------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT *
FROM sys.server_triggers
WHERE name = N'DDL_Audit' )
BEGIN
DROP TRIGGER DDL_Audit ON ALL SERVER;
END;
GO
IF EXISTS ( SELECT 1
FROM sys.tables
WHERE [object_id] = OBJECT_ID(N'dbo.DDL_Events') )
BEGIN;
DROP TABLE dbo.DDL_Events;
END;
-- --------------------------------------------------------------------------------------------------------
-- CREATE THE LOGGING TABLE
-- --------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.DDL_Events
(
DDL_Events_ID INT IDENTITY(1, 1)
NOT NULL ,
EventType NVARCHAR(100) NULL ,
PostTime DATETIME NULL ,
SPID INT NULL ,
ServerName NVARCHAR(100) NULL ,
LoginName NVARCHAR(100) NULL ,
UserName NVARCHAR(100) NULL ,
DatabaseName NVARCHAR(100) NULL ,
SchemaName NVARCHAR(100) NULL ,
ObjectName NVARCHAR(100) NULL ,
ObjectType NVARCHAR(100) NULL ,
CommandText NVARCHAR(2000) NULL,
);
ALTER TABLE dbo.DDL_Events ADD CONSTRAINT pkDDL_Events PRIMARY KEY CLUSTERED (DDL_Events_ID) WITH FILLFACTOR = 100;
GO
CREATE NONCLUSTERED INDEX IX_DDL_Events_PostTime ON dbo.DDL_Events(PostTime) INCLUDE (EventType) WITH FILLFACTOR=100;
GO
DECLARE @extended_property_value NVARCHAR(300);
SET @extended_property_value = SYSTEM_USER + N' '
+ CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
+ N': audit table to log all DDL events on the server.';
EXECUTE sp_addextendedproperty @name = N'MS_Description',
@value = @extended_property_value, @level0type = 'SCHEMA',
@level0name = N'dbo', @level1type = 'TABLE', @level1name = N'DDL_Events',
@level2type = NULL, @level2name = NULL;
GO
-- --------------------------------------------------------------------------------------------------------
-- SET UP SECURITY
-- --------------------------------------------------------------------------------------------------------
--LOGIN
IF NOT EXISTS ( SELECT *
FROM sys.server_principals
WHERE name = N'Auditor'
AND type_desc = N'SQL_LOGIN' )
BEGIN;
CREATE LOGIN Auditor WITH PASSWORD = 'y%&pQNGR*7@Mv5';
END;
--USER
IF NOT EXISTS ( SELECT *
FROM sys.database_principals
WHERE name = N'Auditor'
AND type_desc = N'SQL_USER' )
BEGIN;
CREATE USER Auditor FROM LOGIN Auditor;
END;
--ROLE
IF NOT EXISTS ( SELECT *
FROM sys.database_principals
WHERE name = N'db_auditor'
AND type_desc = N'DATABASE_ROLE' )
BEGIN;
CREATE ROLE db_auditor;
END;
--ADD ROLE MEMBER
ALTER ROLE db_auditor ADD MEMBER Auditor;
--GRANT
GRANT INSERT ON dbo.DDL_Events TO db_auditor;
GO
-- --------------------------------------------------------------------------------------------------------
-- CREATE THE SERVER TRIGGER
-- --------------------------------------------------------------------------------------------------------
/*
Name:
(C) Andy Jones
mailto:andrew@aejsoftware.co.uk
Example usage: -
Perform a DDL action and then run: -
USE master;
SELECT * FROM dbo.DDL_Events;
Description: -
Change History: -
1.0 15/09/2015 Created.
*/CREATE TRIGGER DDL_Audit ON ALL SERVER
WITH EXECUTE AS 'Auditor'
FOR DDL_EVENTS
AS
SET NOCOUNT ON;
DECLARE @data XML = EVENTDATA();
INSERT INTO dbo.DDL_Events
( EventType ,
PostTime ,
SPID ,
ServerName ,
LoginName ,
UserName ,
DatabaseName ,
SchemaName ,
ObjectName ,
ObjectType ,
CommandText
)
SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') ,
@data.value('(/EVENT_INSTANCE/SPID)[1]', 'int') ,
@data.value('(/EVENT_INSTANCE/ServerName)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/LoginName)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/UserName)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/SchemaName)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/ObjectName)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(100)') ,
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(2000)');
GOQuerying the dbo.DDL_Events table, then gives you an audit trail of DDL changes like so:
Safety Catch
How many people can drop your production database? Oh right is that so? Let me repeat the question, how many people can drop your production database?
Do you have sysadmin SQL Logins where the password has never been cycled and who knows that password? Who has knowledge of the Windows domain admin password and can that account authenticate to your production box? Even if you have your permissions fully locked down throughout your estate, you still want to take every known precaution to avoid disaster.
In 15 years working with SQL Server, I have never dropped a database in error. It doesn’t stop that scenario being a recurring nightmare however and you need to take all known precautions to mitigate that risk.
A robust tested disaster recovery strategy.
Use the principle of least privilege. Nobody but a select few members of the DBA team should be able to drop the production database. And even then, they should have to authenticate with a dedicated login with elevated permissions different from their usual.
Colour code your SSMS windows. I use Redgate's SQL Prompt to signify my production connections in red, the universal code for danger.
Another tool you could use is to deploy a safety trigger and that will be described here.
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON;
-- --------------------------------------------------------------------------------------------------------
-- DROP OBJECTS IF THEY EXIST
-- --------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT *
FROM sys.server_triggers
WHERE name = N'Drop_Database_Safety_Catch' )
BEGIN
DROP TRIGGER Drop_Database_Safety_Catch ON ALL SERVER;
END;
GO
-- --------------------------------------------------------------------------------------------------------
-- CREATE THE SERVER TRIGGER
-- --------------------------------------------------------------------------------------------------------
/*
Name:
(C) Andy Jones
mailto:andrew@aejsoftware.co.uk
Description: -
Prohibits the dropping of a database. You first have to drop this trigger.
Change History: -
1.0 23/11/2015 Created.
*/CREATE TRIGGER Drop_Database_Safety_Catch ON ALL SERVER
FOR DROP_DATABASE
AS
SET NOCOUNT ON;
RAISERROR('Are you sure you want to drop this database? First drop the Drop_Database_Safety_Catch trigger if so',16,1);
ROLLBACK;
GOAttempting to drop the database, now gives you the following
Now of course, this does not stop a determined hacker. If they can drop the database, they can also simply drop the trigger first. Where this does help is with those OOPS error, where you think you are dropping the development database and, well you can guess the rest.
If you do actually want to drop a live database (surely a rare event), you now have an additional step to remove the trigger first. You could also extend this idea to prevent others actions such as dropping tables etc, just remember to name the trigger appropriately.
Logon Audit
I recently performed a personal financial audit. It was extremely satisfying to simply delete all those old direct debits for my old energy supplier and payment details for that stag party (I’m British sorry, look it up) I went on years ago that were still present on my account.
Exactly the same principle holds true when performing a SQL Server security audit. The principle of least privilege should be the mantra of every DBA. But before permissions are tied down on the logins that are actually in use, are there any logins in your estate that are simply not used? For many more SQL Server instances that you might think, there will be logins not in use that are members of the sysadmin fixed server role. This is a blatant security risk and these logins need to be removed.
But before that can be done, you need to identify which logins are actually in use. There are a few methods to do this, extended events, SQL Trace, turn on the option to write successful logins to the SQL Error log (not advisable for log pollution) to name but a few. The method described here will use a logon trigger to audit logins.
Logon Trigger Risks
Before going any further, there are risks with adding a logon trigger that you need to be aware of before deploying.
If there is a bug in your logon trigger, nobody can login to your SQL Server. Kind of a big problem if this happens. If this scenario did occur, the logon trigger needs to be dropped immediately. Do this via the dedicated administrator connection (DAC) that bypasses logon triggers or by leaving the SSMS query window open where you create the logon trigger, so you can drop from the same connection. If using the DAC, make sure you are comfortable with this and have tested prior to deploying the trigger, i.e. you can authenticate in Windows to the server itself and you know how to specify the connection string correctly to authenticate via the DAC port. Leaving the SSMS window open from where you created the trigger works because even though new connections are affected, that connection authenticated prior to the trigger creation and will therefore be fine to use.
Once the trigger has been tested and deployed correctly, you could still face similar issues in the future if someone dropped your audit table for example or changed the permissions on it. If anyone could make such untested live changes however, you probably have bigger issues with your deployment process that need to be addressed.
The following with create the necessary logon trigger and audit table in your master database.
USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
SET NOCOUNT ON;
-- --------------------------------------------------------------------------------------------------------
-- DROP OBJECTS IF THEY EXIST
-- --------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT *
FROM sys.server_triggers
WHERE name = N'Logon_Audit' )
BEGIN
DROP TRIGGER Logon_Audit ON ALL SERVER;
END;
GO
IF EXISTS ( SELECT 1
FROM sys.tables
WHERE [object_id] = OBJECT_ID(N'dbo.Logon_Events') )
BEGIN;
DROP TABLE dbo.Logon_Events;
END;
-- --------------------------------------------------------------------------------------------------------
-- CREATE THE LOGGING TABLE
-- --------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.Logon_Events
(
ServerName NVARCHAR(100) NOT NULL ,
LoginName NVARCHAR(100) NOT NULL ,
LoginType NVARCHAR(100) NOT NULL ,
AppName NVARCHAR(128) NOT NULL ,
PostTime_Minimum DATETIME NOT NULL ,
PostTime_Maximum DATETIME NOT NULL ,
Logon_Count BIGINT NOT NULL,
);
ALTER TABLE dbo.Logon_Events ADD CONSTRAINT pkLogon_Events PRIMARY KEY CLUSTERED (ServerName, LoginName, LoginType, AppName) WITH FILLFACTOR = 90;
GO
DECLARE @extended_property_value NVARCHAR(300);
SET @extended_property_value = SYSTEM_USER + N' '
+ CONVERT(CHAR(23), CURRENT_TIMESTAMP, 126)
+ N': audit table to log all DDL events on the server.';
EXECUTE sp_addextendedproperty @name = N'MS_Description',
@value = @extended_property_value, @level0type = 'SCHEMA',
@level0name = N'dbo', @level1type = 'TABLE', @level1name = N'logon_Events',
@level2type = NULL, @level2name = NULL;
GO
-- --------------------------------------------------------------------------------------------------------
-- SET UP SECURITY
-- --------------------------------------------------------------------------------------------------------
--LOGIN
IF NOT EXISTS ( SELECT *
FROM sys.server_principals
WHERE name = N'Auditor'
AND type_desc = N'SQL_LOGIN' )
BEGIN;
CREATE LOGIN Auditor WITH PASSWORD = 'y%&pQNGR*7@Mv5';
END;
--USER
IF NOT EXISTS ( SELECT *
FROM sys.database_principals
WHERE name = N'Auditor'
AND type_desc = N'SQL_USER' )
BEGIN;
CREATE USER Auditor FROM LOGIN Auditor;
END;
--ROLE
IF NOT EXISTS ( SELECT *
FROM sys.database_principals
WHERE name = N'db_auditor'
AND type_desc = N'DATABASE_ROLE' )
BEGIN;
CREATE ROLE db_auditor;
END;
--ADD ROLE MEMBER
ALTER ROLE db_auditor ADD MEMBER Auditor;
--GRANT
GRANT INSERT,UPDATE,SELECT ON dbo.Logon_Events TO db_auditor;
GO
-- --------------------------------------------------------------------------------------------------------
-- CREATE THE SERVER TRIGGER
-- --------------------------------------------------------------------------------------------------------
/*
Name:
(C) Andy Jones
mailto:andrew@aejsoftware.co.uk
Example usage: -
Perform a DDL action and then run: -
USE master;
SELECT * FROM dbo.Logon_Events;
Description: -
Change History: -
1.0 01/09/2015 Created.
*/CREATE TRIGGER Logon_Audit ON ALL SERVER
WITH EXECUTE AS 'Auditor'
FOR LOGON
AS
SET NOCOUNT ON;
DECLARE @data XML = EVENTDATA();
MERGE dbo.Logon_Events AS TargetTable
USING
( SELECT PostTime = COALESCE(@data.value('(/EVENT_INSTANCE/PostTime)[1]',
'datetime'), '19000101') ,
ServerName = COALESCE(@data.value('(/EVENT_INSTANCE/ServerName)[1]',
'nvarchar(100)'),
N'UNKNOWN') ,
AppName = COALESCE(APP_NAME(), N'UNKNOWN') ,
LoginName = COALESCE(@data.value('(/EVENT_INSTANCE/LoginName)[1]',
'nvarchar(100)'),
N'UNKNOWN') ,
LoginType = COALESCE(@data.value('(/EVENT_INSTANCE/LoginType)[1]',
'nvarchar(100)'),
N'UNKNOWN')
) AS SourceData
ON TargetTable.ServerName = SourceData.ServerName
AND TargetTable.LoginName = SourceData.LoginName
AND TargetTable.LoginType = SourceData.LoginType
AND TargetTable.AppName = SourceData.AppName
WHEN MATCHED THEN
UPDATE SET
TargetTable.PostTime_Maximum = SourceData.PostTime ,
TargetTable.Logon_Count = TargetTable.Logon_Count + 1
WHEN NOT MATCHED THEN
INSERT ( ServerName ,
LoginName ,
LoginType ,
AppName ,
PostTime_Minimum ,
PostTime_Maximum ,
Logon_Count
)
VALUES ( SourceData.ServerName ,
SourceData.LoginName ,
SourceData.LoginType ,
SourceData.AppName ,
SourceData.PostTime ,
SourceData.PostTime ,
1
);
GOThis will then give you a result-set something like this when querying the audit table:
I was amazed after adding this trigger at the volume of logins to my SQL Server. It was at that point I started logging APP_NAME() to the audit table too and you can then clearly see the majority of logins come from “Microsoft SQL Server Management Studio - Transact-SQL IntelliSense”.
The logon trigger executes under the security context of a dedicated SQL Server login named Auditor. This avoids the situation where every login would need permissions to insert/update/select the audit table.
Conclusion
The article showed how to use logon and DDL triggers to perform auditing and to prevent the performing of unintended actions. I hope this article was helpful to you. Please drop a comment to describe how you perform similar auditing via alternative methods, I would be very interested to hear from you.
