Auditing SQL Server
-
Hi, I am trying to figure out a way to audit SQL Server. All I want it to return is the login, logout, username, insert, update, and deletes that user did while connected to the server. I want to send this information to a table so I can view it there. If anyone can help me out I would appreciate it. Or if anyone has a good way that they audit their SQL Server I am up for suggestions. Thanks.
-
To record this information, you will either have to design something using triggers: see this article http://www.sqlservercentral.com/columnists/rmarda/auditingtriggers.asp
Use Profiler, which has overhead, and not meant to be used for an ongoing basis,
Or, consider a third-party product, such as Lumigent's Entegra (but this will cost some buck$$)
-----------------------------------------------------------------------
If you are looking for login audit trail information only, you set the audit level on SQL Server, via EM -> Properties -> Security Tab. Here you can select to audit Successful Logins, Logon failures, or both. It will record anytime a user logs on, or attempted to logon and fails, in the SQL Server error log. Need to recycle SQL Server first.
There you will see the date and time, user, and some other info, for example:
Login succeeded for user 'MYDOMAIN\sqllogin'. Connection: Trusted.
You could then dump this info into a table and manipulate it from there. I will post an example below:
Hope this helps!
/*First create simple table based on the parameters of the output of the SP called sp_errorlog*/
Create Table #table1 (
Output Varchar(255),
Row Int)
go
/*Then insert into the new table the results of sp_readerrorlog*/
Insert into #table1 (Output, Row) Exec master..sp_readerrorlog
/*Query the new table by specifying only rows that are like "logon" for the dates (based on a substring of output col)
between date1 and date2*/
select substring(output,1,10) As date,
substring(output,11,12) As time, output
from #table1
where output like '%logon%'
-- uncomment the following for date parms:
--and substring(output,1,10) between '2002-07-18' and '2002-07-23'
select * from #table1 where output like '%login%'
drop table #table1
-
I need a little more information than just the login information though. I need the insert, update, delet information and the username that they used when they were on the server.
-
Dave:
Please see my updated post, which I was in the middle of editing, when I reviewed what you're looking for.
-
I see here in SQLServerCentral.com a script that make a triggers on all tables you have in your DAtabase .. and of course make an Audit table for you. search for that script.
Alamir Mohamed
Alamir_mohamed@yahoo.com -
You also may want to check out some of the tools here: http://www.apexsql.com/
"I will not be taken alive!" - S. Hussein
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply