June 19, 2012 at 12:17 am
Hi All
I want to know that how can we get to know that when we made modification in database which user can login . i know that there is a log automatic create in database but i donot know how i can fetch the logs and get the logs details
June 19, 2012 at 12:23 am
Hi,
1. select * from fn_dblog(null,null) can be of use for you
2. Running SQL Server Profiler can also be of use
3. Windows logs which you can filter for SQL server
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
June 19, 2012 at 12:23 am
Sounds like you are looking for a very low level detailed activity log of the database changes and transactions with user names linked to them?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 19, 2012 at 2:56 am
User information is not logged in the database transaction log.
You will need to create an audit process and/or add a column to the tables to store the last modified user.
June 19, 2012 at 7:44 am
if your looking for schema changes then you can create a DDL trigger on the database thats what i use to audit SP, Table, Trigger and index creations amendments etc
***The first step is always the hardest *******
June 19, 2012 at 7:46 am
PS. there is a report that you can run that will also show what schema changes have been made against your database but it will only show you the information back to the last sql server reboot
from SSMS
RMC on your database
Select Reports
General
Schema changes
***The first step is always the hardest *******
June 19, 2012 at 7:55 am
SGT_squeequal (6/19/2012)
PS. there is a report that you can run that will also show what schema changes have been made against your database but it will only show you the information back to the last sql server reboot
Not quite. It's from the default trace. Trace stores 5 files of 20 MB each, rolls a file over when it reaches 20MB or the server restarts. So you could have data back 5 restarts, if the server's not very active or back 10 min or less if it's extremely active.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2012 at 7:59 am
Thanks Gila, not noticed that it wokred on size just noticed last tiem i restarted SQL server the report was empty
***The first step is always the hardest *******
June 19, 2012 at 8:22 am
SGT_squeequal (6/19/2012)
Thanks Gila, not noticed that it wokred on size just noticed last tiem i restarted SQL server the report was empty
Default trace doesn't just store schema chances, so entirely possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply