DBCC Usage for Auditting 2

[ousoonerjoe]

I've recently learned about the existence of the DBCC calls. From what I am discovering, this has great use in Audit trails. What I am running into, is being able to push the output to a table. Ideally, I just need the 'EventInfo' field, but will take all and work with it as needed. Here's a couple samples of what i've been trying to pull off:

DECLARE @Id INT;
DECLARE @Et VARCHAR(50);
DECLARE @Parms    INT;
DECLARE @T  VARCHAR(8000);

SET @Id = (SELECT @@SPID);

DECLARE Curse CURSOR FOR DBCC INPUTBUFFER (@Id)
OPEN Curse
FETCH NEXT FROM Curse INTO @Et, @Parms, @T

WHILE @@FETCH_STATUS = 0

      BEGIN

      INSERT INTO TestAudit(EventType, Params, EventInfo)
      VALUES (@Et, @Parms, @T)

      FETCH NEXT FROM Curse INTO @Et, @Parms, @T
      END
CLOSE Curse;
DEALLOCATE Curse;

[i]Something like this is out too:[/i]

DECLARE @Id INT;
SET @Id = (SELECT @@SPID);

SELECT (DBCC INPUTBUFFER (@Id)) INTO TestAudit

Any assistance is welcome.

Thank you.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

I think you want to use "Insert/Exec" like this:

DECLARE @Id INT;
Declare @SQL VarChar(8000)

SET @Id = (SELECT @@SPID);
Create Table #Temp(EventType VarChar(100), Parameters VarChar(100), EventInfo VarChar(7000))
Set @SQL = 'DBCC INPUTBUFFER (' + Convert(VarChar(20), @Id) + ')'
Insert Into #Temp Exec (@SQL)

Select * From #Temp
Drop Table #Temp

Of course, if this works with a temp table, you could easily change this to insert in to your real (permanent) table.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

You da Man, George!

That's exactly what I was looking for. Now then, I can add this to the Stored Procs to get some better auditing in place on those.

Thanks.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

Just out of curiosity, what's the "big picture" goal here?

One thing you may want to look at is....

Select object_Name(@@procid)

If you run that in a query window, you'll get NULL. But, if you run this in a stored procedure, it will return the name of the stored procedure for you.

Ex:

Create Procedure AnyNameYouWant
As
SET NOCOUNT ON
Select object_Name(@@procid)
go
Exec AnyNameYouWant
go
Drop Procedure AnyNameYouWant

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Does it return the Value of each parameter passed into it at run time? That's what we're ultimately trying to get to. User says they entered one thing; We show they entered something else. We need to see what values were passed into the stored proc and Which stored proc was called.

We've tried using Tracing, but it only returns the Parameter name and not the values.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

Does it return the Value of each parameter passed into it at run time?

No. Just the procedure name.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Need the values. This seems to be returning the values so far in my testing. We'll see how it does when being fired from an application.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[yelworcm]

you may want to have a look at SQL Profiler, then, if you are trying to get the command parameters, and all. Set up the trace, as you want it in the GUI, then script the trace, and run the output to a file somewhere. Collect that file, import into a database, and do any analytics you like on it at your leisure.

 

[markros]

I found this article that may help

http://www.sqlmag.com/Article/ArticleID/25533/sql_server_25533.html