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
Database Blog 