Question :
I am looking for a way to create a logon trigger in SQL Server.
Basically i need to do is when it matches some users or profiles on logon it send an alert e-mail.
I have found some things for Oracle but nothing for SQL Server.
Thanks.
Answer :
Wouldn’t it be better to store logins in a table and then provide a report for that table? Or perhaps, email a report that shows a list of logins for that day.
/* create test login */
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
GO
GRANT VIEW SERVER STATE TO login_test;
GO
USE AUDIT
GO
/* Creating audit table*/
CREATE TABLE audit.dbo.LogonAuditing
(
SessionId int,
LogonTime datetime,
HostName varchar(50),
ProgramName varchar(500),
LoginName varchar(50),
ClientHost varchar(50)
)
GO
/* Create Login Audit Trigger */
CREATE TRIGGER connection_audit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@ClientHost varchar(50),
@LoginType varchar(50),
@HostName varchar(50),
@AppName varchar(500)
SET @LogonTriggerData = eventdata()
SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()--,program_name()
INSERT INTO audit.dbo.LogonAuditing
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost
END;
/* in case you want to drop the trigger
drop trigger connection_audit_trigger on all server
go
*/
try this, as per the link in comment, you can creat a trigger and I have added some script to send mail
USE master; GO CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' GO GRANT VIEW SERVER STATE TO login_test; GO GO CREATE TRIGGER mail_sending_trigger ON ALL SERVER WITH EXECUTE AS 'login_test' FOR LOGON AS BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Adventure Works Administrator', @recipients = 'test@AdventureWorks.com', @body = 'The stored procedure finished successfully.', @subject = 'Automated Success Message' ; END;