- Run SQL Managements Studioand execute the following code in Transact-SQL:
CREATE DATABASE LogonAudit /* Creates db for storing audit data */
CREATE TABLE LogonAuditing /* Creates table for logons inside db */
CREATE TRIGGER [LogonAuditTrigger] /* Creates trigger for logons */
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()
INSERT INTO [LogonAudit].[dbo].[LogonAuditing]
In order to see the collected information about logons in the SQL Management Studio run the following script:
You can save data to CSV format after that.
- Run Netwrix Auditor, Navigate to Reports → Choose “SQL Server” → “All SQL Server Logons” →Click “Subscribe”.
- Define recipient and filters, save subscription. You can receive it via e-mail or have it delivered to a specified shared folder according to the schedule you set.
Surpass Creating Logon Triggers on SQL Server to Gain Control over Access
If someone has accessed your SQL Server database directly, you need to know about it immediately. Database administrators who gain access can perform illicit actions; for instance, they can drop tables or create triggers without your notice. Native tools enable you to create a SQL Server logon trigger to audit access events on all servers and then keep an eye on logon events and trigger execution using SQL Server Management Studio (SSMS). But be ready to spend significant time creating a trigger code in Transact-SQL and then painstakingly poring through audit information to spot aberrant access.
Netwrix Auditor for SQL Server eliminates the need to create any DDL triggers, including logon triggers in SQL Server, to enable auditing of your SQL Server. The solution helps you gain pervasive visibility into who attempted to access any of your SQL Server instances by showing the original logon attempt, when and where it was made, who made it, and whether it was successful or failed. With out-of-the-box insightful reports and Google-like search, you can identify and chase down emerging threats in no time. Plus, the solution stores all your logs for years in a cost-effective two-tiered storage (SQL database + file-based) and enables you to easily access them at any time.