SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Audit Login
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/19/2013 :  03:10:11  Show Profile  Reply with Quote
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

India
245 Posts

Posted - 02/19/2013 :  03:14:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 02/19/2013 :  03:37:35  Show Profile  Reply with Quote
You can use SQL Server Profiler by the selection of Security Audit Event

--
Chandu
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/19/2013 :  03:52:28  Show Profile  Reply with Quote
But all time profiler execution is impossible.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/19/2013 :  04:12:42  Show Profile  Reply with Quote
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

India
245 Posts

Posted - 02/19/2013 :  04:21:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 02/19/2013 :  05:02:47  Show Profile  Reply with Quote
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

Edited by - bandi on 02/19/2013 05:15:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000