Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Audit Login

Author  Topic 

shaggy
Posting Yak Master

248 Posts

Posted - 2013-02-19 : 03:10:11
Hi friends,

I want to audit whichever login has logged on
I can able to get the login failed details like (Password did not match) in error log
but specifically i need to audit logged on users

should i need to enable trace so that it will be captured in error log.

or any other approach is highly appreciated.

shaggy
Posting Yak Master

248 Posts

Posted - 2013-02-19 : 03:14:39
By enabling security option in server properties i can able to get failed and successful detals in error log

any other approach.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-19 : 03:37:35
You can use SQL Server Profiler by the selection of Security Audit Event

--
Chandu
Go to Top of Page

shaggy
Posting Yak Master

248 Posts

Posted - 2013-02-19 : 03:52:28
But all time profiler execution is impossible.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 2013-02-19 : 04:12:42
Do you wanted to know what users logged in at a particular time or wanted to trace all the users who are logged on. You can run the following query if you wanted to check what user logged on and what action is he performing at a given time. If you wanted to trace, you can get the info by many ways.

SELECT
d1.session_id,
d3.[text],
d1.login_time,
d1.login_name,
d2.wait_time,
d2.blocking_session_id,
d2.cpu_time,
d1.memory_usage,
d2.total_elapsed_time,
d2.reads,d2.writes,
d2.logical_reads,
d2.sql_handle
FROM sys.dm_exec_sessions d1
JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id
CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3
Go to Top of Page

shaggy
Posting Yak Master

248 Posts

Posted - 2013-02-19 : 04:21:38
Thanks srimani & bandi

I want to automate the particular login to be audited so that in future i can able to track.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-19 : 05:02:47
Check A Simple Auditing Example section in the below link
http://www.bradmcgehee.com/2010/03/an-introduction-to-sql-server-2008-audit/

You can use Logon Trigger to audit logon details.... Before using logon trigger you must know about LOGON Trigger...
http://blog.sqlauthority.com/2009/05/27/sql-server-interesting-observation-of-logon-trigger-on-all-servers/
http://blog.sqlauthority.com/2009/06/26/sql-server-interesting-observation-of-logon-trigger-on-all-servers-solution/
http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/
--
Chandu
Go to Top of Page
   

- Advertisement -