February 18, 2010 at 9:16 pm
I have a security concern:
Whoever made any attempts to log onto my server/database, I need to record that info, no matter how the user access it, failed or succeeded, even through a valid account and via management studio.
Can this be accomplished? my thought is it would be great if I can even track down the person's computer info like computer name, IP address, etc.
Thanks. Any hint would be appreciated.
February 18, 2010 at 9:41 pm
halifaxdal (2/18/2010)
I have a security concern:Whoever made any attempts to log onto my server/database, I need to record that info, no matter how the user access it, failed or succeeded, even through a valid account and via management studio.
Can this be accomplished? my thought is it would be great if I can even track down the person's computer info like computer name, IP address, etc.
Thanks. Any hint would be appreciated.
Yes, Go to Server Properties --> Under security -->Check Both Failed and Successful Login. By default only failed logins, Logs will fill up quickly, you will see client IP, i am sure about machine name, once you change the settings you may need to recycle SQL services to take effect.
EnjoY!
February 19, 2010 at 12:58 am
Try this
declare @tbl table ( logdate datetime, processinfo nvarchar(20), login_text nvarchar(max))
insert into @tbl (logdate, processinfo, login_text)
exec sp_readerrorlog
select * , '[' + SUBSTRING( login_text , PATINDEX ( '%CLIENT:%', login_text)+7, len(login_text)) IP_Address from @tbl where login_text like '%login%'
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 19, 2010 at 5:25 pm
Bhuvnesh (2/19/2010)
Try this
declare @tbl table ( logdate datetime, processinfo nvarchar(20), login_text nvarchar(max))insert into @tbl (logdate, processinfo, login_text)
exec sp_readerrorlog
select * , '[' + SUBSTRING( login_text , PATINDEX ( '%CLIENT:%', login_text)+7, len(login_text)) IP_Address from @tbl where login_text like '%login%'
Thanks.
Is there a similar sp like this one for reading all log instead of only error log?
February 19, 2010 at 9:20 pm
You may want to examine the default trace to answer your questions. Here are a few links to assist you:
Default trace - A Beginner's Guide
By Adam Haines, 2010/06/11 (first published: 2008/11/11)
at:
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
and:
SQL Server 2005, List of Events Captured by the Default Trace
at:
Further:
Who has accessed my 2005 server?
By Jack Corbett, 0001/01/01
at:
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply