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.
-- Creating audit table
CREATE TABLE LogonAuditing
-- Creating DDL trigger for logon
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER
DECLARE @LogonTriggerData xml,
SET @LogonTriggerData = eventdata()
SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()--,program_name()
INSERT INTO DemoDB.dbo.LogonAuditing
Audit 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'
-- Preventing the connections from SSMS
IF @AppName = 'Microsoft SQL Server Management Studio'
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?