March 3, 2017 at 5:20 am
Hello,
I would like to audit all user connections(Login name, Hostname, IP,Program,data and time) to a particular database(say AdventureWorks) on server.Tried to setup SQL Auditing, which is not capturing all the required details(missing hostname,IP and program).Even tried to setup Logon triggers(as mentioned in below thread),which is blocking all user connections to the database.
https://www.sqlservercentral.com/Forums/Topic1146494-146-1.aspx
Can someone please help?Any scripts/suggestion is greatly appreciated
March 3, 2017 at 6:11 am
Extended Events. You can easily capture the logins and filter to only a single database. It's the least obtrusive way to get this done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2017 at 7:11 am
Thanks..We are using SQL Server 2008 R2.Is there any script to create extended events to accomplish this ?
March 6, 2017 at 8:10 am
Yes, you can get SSMS 2016 (free, should work with 2008 R2.
Then: https://voiceofthedba.com/2015/12/16/tracking-logins-with-extended-events/
March 7, 2017 at 8:25 am
Below code works fine on SQL Server 2012,but for 2008R2 it is not working as "sqlserver.login" is an invalid object & I think we cannot filter it by DB name as well.Can someone please suggest?
CREATE EVENT SESSION [Audit] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
WHERE ([sqlserver].[database_name]=N'TestDB'))
ADD TARGET package0.event_file(SET filename=N'F:\SQL14DEV02\somefile.xel')
WITH (STARTUP_STATE=ON)
GO
March 7, 2017 at 8:44 am
I don't have an R2 instance handy, but there are some queries in here to examine all events: https://technet.microsoft.com/en-us/library/dd822788(v=sql.100).aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply