Auditing Stored Procedure Execution

Question

Auditing Stored Procedure Execution

yaweah

Default port

Points: 1489
More actions
February 23, 2004 at 3:26 pm

#178220

Hell All, I have this task that I want to accomplish. At a glance I thought I was not an issue but it's starting to be one. All table access are done through stored procedures which means that no user will have direct access to my tables. Now I want to audit every execution of the stored procs. So if a user passes in his/her parameters, i want to capture these parameters as well as the parameter name. So i have this proc but it's not working. What the proc does is, it generates all the input parameters names from the information_schema view and with these name, it trys to get the associated values but I can't get the values. Here is the sample code:
CREATE PROCEDURE SP_AUDIT_PRO
(
@p_In_PAR1  INT,
@p_In_PAR2  INT,
@p_In_PAR3  INT,
@p_In_PAR4  BIT
)
AS
SET NOCOUNT ON
IF @p_In_PAR1 <> 0
BEGIN
  --temp table ID Values
  DECLARE @l_TempIDValue_Int INT,
   @l_ParameterName_Vch VARCHAR(60),
   @l_ParameterValue_Vch VARCHAR(100)
  --create temp table to store the store proc parameters
  CREATE TABLE #SP_PARAMETERS
    (
     [ID]  INT  IDENTITY(1,1) PRIMARY KEY,
     PARAMETER_NAME NVARCHAR(128)
   &nbsp
--results of the input parameters and parameter values.
  CREATE TABLE #SP_PARAMETERS_NAME_VALUE
    (
     [ID]  INT  IDENTITY(1,1) PRIMARY KEY,
     PARAMETER_NAME NVARCHAR(128),
     PARAMETER_VALUE VARCHAR(100)
   &nbsp
  SET ROWCOUNT 0
  --populate the temp table with the list of input parameters for this stored proc
  INSERT   #SP_PARAMETERS(PARAMETER_NAME)
  SELECT   PARAMETER_NAME
  FROM   INFORMATION_SCHEMA.PARAMETERS
  WHERE   SPECIFIC_SCHEMA = USER_NAME(OBJECTPROPERTY(@@PROCID, 'OWNERID'))
    AND SPECIFIC_NAME = OBJECT_NAME(@@PROCID)
          AND PARAMETER_MODE = 'IN'
  ORDER BY ORDINAL_POSITION ASC
  --get only one record
  SET ROWCOUNT 1
  --get the id of the first record in the temp table
  SELECT @l_TempIDValue_Int = [ID]
  FROM   #SP_PARAMETERS
  --loop through the temp table and for each stored proc parameter, get the associated value
  WHILE @@ROWCOUNT <> 0
  BEGIN
   SET ROWCOUNT 0

   --Get the parameter name of the current records
   SELECT @l_ParameterName_Vch = PARAMETER_NAME
   FROM    #SP_PARAMETERS
   WHERE   [ID] = @l_TempIDValue_Int
--------------------------------THIS IS WHERE I AM HAVING A PROBLEM.
---I CAN NOT GET THE VALUE OF THE PARAMETER, INSTEAD I AM ONLY ABLE ---TO GET THE NAME OF THE PARAMETER
HOW DO I GET THE VALUE OF THE PARAMETER
   --get the parameter value
   SET @l_ParameterValue_Vch = (SELECT @l_ParameterName_Vch)
   --for each parameter, gave the associated input value. accumulate the list of parameters and their associated values
   INSERT #SP_PARAMETERS_NAME_VALUE(PARAMETER_NAME,
         PARAMETER_VALUE)
   VALUES (@l_ParameterName_Vch, @l_ParameterValue_Vch)
--------------------------------------------------------------------------
   --delete parameter name from temp table to get the next record
   DELETE #SP_PARAMETERS
   WHERE [ID] = @l_TempIDValue_Int
   SET ROWCOUNT 1

   --get the next parameter name from the temp table
   SELECT @l_TempIDValue_Int = [ID]
   FROM   #SP_PARAMETERS

   --call the stored proc audit
  END
  SET ROWCOUNT 0
  SELECT [ID],
   PARAMETER_NAME,
   PARAMETER_VALUE
  FROM #SP_PARAMETERS_NAME_VALUE
END
SET NOCOUNT OFF

If any one out there have an idea how to get this done, please HELP. Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Robert W Marda SSChampion Points: 13413 More actions · Answer 1

I'm not sure you can get the parameter values the way you are trying. I have never tried and don't know.

I will make some other suggestions. You can put a statement in each SP that inserts a row to a parameter table as well as from which sp the parameters came from.

Another way is to have the profiler send all SP calls to a table in SQL Server and get the parameters of each SP that is executed.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

yaweah Default port Points: 1489 More actions · Answer 2

yaweah

Default port

Points: 1489

February 23, 2004 at 3:56 pm

#495228

How can I implement your first option?

wz700 SSCertifiable Points: 7133 More actions · Answer 3

wz700

SSCertifiable

Points: 7133

February 23, 2004 at 5:15 pm

#495230

Profiler is an option.

Robert W Marda SSChampion Points: 13413 More actions · Answer 4

create a table, perhaps called tblSPParameters with columns such as ParameterName varchar(30), ParameterValue varchar(30), SPName varchar(50), DateExecuted datetime with a default value of getdate(), and any other info you need.

Add to your SP the following:

INSERT INTO tblSPParameters (ParameterName, ParameterValue, SPName)

SELECT '@LastName', @LastName, 'spNameSearch'

If you use this method then you would need one insert per parameter which could be a bit much for performance. The other alternative is to have multiple columns for ParameterName and ParameterValue in the table tblSPParameters so that you can use one INSERT statement.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

yaweah Default port Points: 1489 More actions · Answer 5

There is one main thing that i did not mention in my original post. I don't want to hard-code this auditing. That is why I am using the Informational_Schema view to get the meta data (parameter list) per each stored proc so that I will not have an overhead in maintening changes to the stored proc. For instance if i hard-code with an insert statements with each parameter name, when i add additional parameters to the store proc i have add additional insert statements within the stored proc. Can some help me on the type of template, filters, events, etc that I need to setup in profile to capture this type of audit?

I still prefer my first solution but if that is not going to work i am willing to try anything. HELP.

Thanks guys.