I need to audit and record who connects to the actual SERVER (not the database instance) and record it in a table in SQL Server . With a LOGON trigger you only gets who connect to the instance but i need to know who connects to the actual server. Is there a way I can import Windows security event viewer log to SQL Server? or are there any other way I can record who connects to the server in SQL Server?
Ok. There is a PoSH option. You can use the [PoSH guru's] method to perform this. Create a table to host the values. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[EventViewer]( [Index] [int] NULL, [Time] [datetime] NULL, [EntryType] [varchar](MAX) NULL, [Source] [varchar](MAX) NULL, [InstanceID] [varchar](MAX) NULL, [Message] [varchar](MAX) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO You should be able to then execute the PoSH script (assuming you have access to the server, you have AUDITING turned on and the relevant modules loaded) $ServerEvents = 'SERVER1' $DBServer = 'DBAServer' $DB = 'DBA' $variable = ( Get-EventLog -ComputerName $ServerEvents -LogName Security -After “11-04-2014“ | where eventid -EQ "528" | select index,TimeGenerated,EntryType,Source,InstanceID,Message); $valuedatatable = Out-DataTable -InputObject $variable ; Write-DataTable -ServerInstance $DBServer -Database $DB -TableName EventViewer -Data $valuedatatable I have changed the code that Laerte uses to include only the Successful logon event. It would also be a good idea to read the blog as he also describes now to enumerate multiple servers NOTE: Code is "as is" and no warranty is provided. Please test in a non-production evironment first. :