As a security part, recently I worked with login details and the auditing for the same. I was needed to capture each events for login statistics like login name, the time when logon, the program through established connection, session and host or client IP. These event is raised when user sessions make connections with SQL Server instances where we configured LOGON trigger.
This will helpful to us to make auditing the logon details for each connections. For the small demo let us create required objects and implement it.
USE DemoDB
GO
-- Creating audit table
CREATE TABLE LogonAuditing
(
SessionId int,
LogonTime datetime,
HostName varchar(50),
ProgramName varchar(500),
LoginName varchar(50),
ClientHost varchar(50)
)
GO
USE Master
GO
-- Creating DDL trigger for logon
CREATE TRIGGER LogonAuditTrigger
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 DemoDB.dbo.LogonAuditing
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost
END
GOAudit table and the trigger to fill that table is created. Now it is time to evaluate ,test it and review the audit table.
We have all details for the login events from the table. Also one more interesting thing is as we can prevent unwanted user logins and connections with SQL Server. This prohibition can be for the login, program or host, for that we just need to add some code in logon trigger for conditions to make rollback at that time like following,
-- Preventing 'sa' login
IF @LoginName = 'sa'
BEGIN
ROLLBACK;
END
-- Preventing the connections from SSMS
IF @AppName = 'Microsoft SQL Server Management Studio'
BEGIN
ROLLBACK;
END
For first criteria to prevent login ‘SA’ , if connection made for the same and the message fired at the time of event login,
You can make you criteria as per requirement as I implemented for login and program connections.
Are you using Logon Trigger?