Audit the SQL server logins

Here is a trigger that will enable you to log the alter database login activity on a SQL instance. The trigger will insert a record for each alter login activity. It will log which username has changed the login and when. For this to work, Audit table needs to be created before hand.

Script to create Audit table:

USE [Enter DB Name here]
GO

CREATE TABLE [dbo].[Audit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[QueryText] [varchar](260) NULL,
	[EventDescription] [nvarchar](260) NULL,
	[eventtime] [datetime] NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Script to create the trigger:

Use Master
GO

If Exists (Select * From sys.server_triggers where [name] = 'DDL_Trigger_Login_Changed')
    DROP TRIGGER [DDL_Trigger_Login_Changed] ON ALL SERVER
go

CREATE TRIGGER [DDL_Trigger_Login_Changed]
ON ALL SERVER
AFTER CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
AS
Declare @results varchar(max)
Declare @subjectText varchar(max)
SET @subjectText = '*ALERT* DATABASE LOGIN changed on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))

/* Insert the change event into the Audit table */
INSERT INTO [Enter DB Name here].[dbo].[Audit]
           ([QueryText]
           ,[EventDescription]
           ,[eventtime])
     VALUES
           (@results
           ,@subjectText
           ,CURRENT_TIMESTAMP)

/* Send an email when a login is altered */
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Email profile',
    @recipients = 'Enter Email Address here',
    @body = @results,
    @subject = @subjectText,
    @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s