Change every SP with a script 1

[timfoster]

Hi Folks,

I need to add some auditing to a clients database. The sp's to actually do the audit are no problem. However, I need to insert the calls to the audit procs into every stored proc in their database and there are over 300!!!

In addition, I'll need to be able to remove the audit calls on demand too!

So, what I'm asking is does anyone know how to script inserting a line into every sp on the db and subsequently remove it?

 

[gmmastros]

I suggest you do this work in a front end language like VB, C#, etc.... I think it will be a lot easier that way.

[tt][blue]In addition, I'll need to be able to remove the audit calls on demand too![/blue][/tt]

Instead of removing the audit calls, I would recommend that you include a "config" table and have your audit calls check the config table before the actual call. Something like this...

[tt][blue]
If Exists(Select 1 From Config Where ConfigName = 'Audit' And ConfigValue = 'Yes')
Begin
-- Put your auditing calls here
End
[/blue][/tt]

This way, you'll be able to enable/disable the auditing with a simple change of a value in a table.

-George

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

 

[timfoster]

Thanks for the feedback George.

Is it possible to read the contents of an sp though vb? If you know how could you dump an example for me?

Nice idea about checking a table. Unfortunately, I can't. Their licence agreement prevents them from changing the code. They want to be able to run the routine to insert the calls and then remove it again so it was never there. The vendor doesn't check the modification dates of the object when they do an update, but their consultants may inspect them when they're on site.

 

[gmmastros]

What version of SQL Server are you using?

-George

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

 

[gmmastros]

If you are using SQL2005 or SQL2008, you can use....

Select name, 
       Object_Definition(Object_Id) As Code 
From   sys.procedures

That will return a list of stored procedures and it's code. If you run this query in SQL Server Management Studio, the code will probably be cut off because the management studio likes to truncate data for it's display. But, if you run this from within VB (or any other front end language), you will see it all.

From with VB, you would execute that query exactly the same way you would run any other query in the database.



-George

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

 

[timfoster]

Cool! Thanks George. You're a genius!

It's amazing what you learn each day with SQL!

 

[SQLSister]

Personally I think your audit process is flawed. Auditing should be done through triggers not sps. If someone makes a change to the database that is not thorugh the usual sps that run you will not catch it. That means if you have someone make an ad hoc query to deliberately mess up the data or to commit fraud, it won't be audited. If you allow table access (because your sps have dynamic sql), then this could be an ever more likely scenario. AUditing shoudl alawys be at the base datbase level never through the application and stored procs or you risk missing some of the changes, probably the changes you will most need to see.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Hey SQLSis, can you briefly describe how you have your auditing set up. I currently don't do any auditing in my database, but some customers are beginning to ask for it. So.... I'd like to do it right the first time. Specifically...

How do you have your audit tables set up? Is it a copy of the real tables with a couple more columns added (if so, what columns), or is it something different?

Can you describe (preferably with code) how you have your triggers set up?

-George

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

 

[markros]

These two links are generic, but may be of some help

http://articles.techrepublic.com.com/5100-10878_11-6175865.html

Here is a link for Audit in SQL Server 2008

http://msdn.microsoft.com/en-us/library/dd392015.aspx

PluralSight Learning Library

 

[timfoster]

Ah yes, SQLSister, but that's assuming the purpose here is to audit the data. In that case I would agree wholeheartedly, but as we're looking to audit the usage of the sp's, triggers wouldn't help much.

 

[SQLSister]

George, concerning audit tables, First we set them up on each table not one big one for everything because these tables are hit with every data change and one big table would be a bottleneck.

We do not store the whole record. What we store is first a record concerning the instance of the change (If I do an insert of 20,000 records, this table will get one row). This records the instance id which is autoincremented, the user doing the change, the application the change came from, and the date. Since we have many applications that potentially could change data in a table, it is useful when things start to go bad to see if all the bad records came from the same application.

Then we have a detail table which records the instance id, the primary key for the row being changed, the old and new values and the field being changed. So insert one row with 20 columns and there would be twenty rows in this table associated with that insert. Update one column and there would be one row. Since we don't know in advance what the data type of the column is they are all stored in ntext (which will be varchar (max) probably when we move all our dbs to 2008 and stop working in compatibility mode for 2000.

I don't do this part and don't have access to the code, but I understand that we have code that autogenerates dynamically a new audit table when a new table is added. This code is run as part of the deployment process when changes are made to the completely audited database.

We also have partial auditing on some client specific databases depending on the nature of the tables, these audit tables are created from the same script but not done unless specifically asked for.

Code is proprietary and I can't put it here on a public forum.

The advantage of our system is that all audit tables are exactly alike making it easy to autogenerate them and meaning we don't have to change structure when the audited table changes structure. They are a little difficult to query, but we have the queries to extract the data we need already written, so that's not a huge problem for us. Being able to research who made a change and when and what application is priceless as is being able to undo bad data changes (sure came in handy on one memorable occasion when someone accidentally changed all the records in one table (the one with user logins naturally) to relate to the same person. Oops someone forgot a where clause but production was down less than five minutes.) Also has been handy to undo a bad import due to the client sending data in the wrong columns (first name data belongs in the first name column not the last name column) or when they accidentally changed their own unique identifiers and they wondered why Sally Smith was now John Jones.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

Thank you SQLSister. I haven't completely made up my mind how I will implement this, but your post is very valuable to me. My auditing will probably look very similar to what you just described.

Thanks again.

-George

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

 

[SQLSister]

George,remember that you are going to be querying tables that are constantly being written to, so be very careful about locking them with your queries to pull data back out.

"NOTHING is more important in a database than integrity." ESquared