I am looking for a way to query database logs for Pervasive SQL and MSDE.

I want to be able to review who has accessed these databases and maybe get reports.  Does anyone know of software that can read the logs?  Are there even logs to be read?  Does Microsoft provide any built-in reporting?
Thanks
rhouston0872Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SashPCommented:
Hi rhouston0872

There is no built-in reporting for Microsoft SQL Server.  The transaction log records all (well most) data changes in the database including who and when.  Unless C2 auditing is turned on SQL Server does not track who logs on and unless they make a change to the data then there will be no trace that they were in the database.

Software for reading SQL Logs.

I would recomend Lumigent Log Explorer for SQL Server http://www.lumigent.com/products/le_sql.html

You may consider ApexSQL Log http://www.apexsql.com/apex_sql_log.htm but I prefer the Lumigent product.

As for Pervasice SQL.

I do not have any experience so I am unable to assist you. You may be better served posting the pervasive sql question to the  databases topic area.

Cheers Sash
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wael_tahonCommented:
You can use SQL Profiler

Start a new trace and select the Security Audit Events

Best regards
Wael Tahon
0
roshkmCommented:
There is SP_Who2 procedure which you can call. It will give you the current login used, machine name, CPU usage etc.

May be you have to create a job which will call this function and insert into a table, from where you can do auditing.

something like :

CREATE table MyTable
(SPID int, Status varchar(1000), Login varchar(1000), HostName Varchar(1000), BlkBy Varchar(1000),
DBName varchar(1000), Command varchar(1000), CPUTime int, DiskIO int, LastBatch varchar(1000),
ProgramName varchar(1000), SPID_1 int )

--Configure this as the job:
insert into MyTable exec sp_who2


Regards,
Rosh.K.M

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SashPCommented:
wael_tahon,
Using Profiler is an unacceptable overhead for auditing a database, it should be reserved for identifying and analysing problems within a SQL installation and if possible be used on development servers and only used in production when all other avenues have been exausted.

rosh,
Using sp_who or sp_who2 only tells you who is in the database at a specific point in time not who has logged in over a period of time, so either you will miss connections (especially well designed transaction processing apps which only maintain connections for milliseconds) or you will generate massive amounts of data and still not guarantee who has accessed your database you should not use this method especially if the login tracking is for compliance reasons.

Although both of these suggestions may be able to be used to acheive a result similar to the request they are not suitable for an enterprise or production database.

Cheers Sash
0
roshkmCommented:
Sash,
SP_Who2 will get u who is connected at a specific time, true. But if u increase the freq of the job, u can capture the minutest details. It purely depends on the necessity of the author. There are tools available as well as built in apps to capture this, but will cost you performance. The job i suggested can be configured in either way based on the need. The temp table can be cleared after a specific time, hence clearing of junk data. All these are upto the DBA to decide.


It is difficult to take the details of who connected from the transaction log, since the same will be updated and cleared once the transaction is complete.

Regards,
Rosh.K.M

0
SashPCommented:
Your statement about the transaction log being cleared once a transaction is complete is not actually correct.  The recovery model chosen for a database determines how much of the transaction log inis retained.  The only time that the log is not retained is when simple recovery model is selected. Normally the transaction log is cleared when a backup of the transaction log is performed or when a manual truncate of the log is executed.

I would not suggest using the log for determining who is in your database, as the log does not record data reads only changes to the database.

However creating an extra process that hits the database continually does not guarantee capturing who is in the database.  As you increase the frequency of the sp_who(2) execution the increase the likely hood that you will catch the user, but efficient systems do not necessarily maintain connections for a long period of time.

I do not believe that this is an appropriate approach.  C2 Auditing is as far as I am aware the only method that is suitable for compliance reasons that is suitable for this task.

Regards
Sash
0
roshkmCommented:
When i said 'Transaction complete', i meant after the backup or defrag operation etc in case of Full model and in case of a successful query execution in case of Simple model.

I hope that our owner is not chasing a virus..  :)

Regards,
Rosh.K.M
0
rhouston0872Author Commented:
Thanks for all the responses.  I'll have to wade through this and try and figure out all your answers, much less the solution.  By the way, I'm not chasing a virus.
I'll come back later today with any followup questions and hopefully find a solution.
I appreciate all the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.