Control Changes To SQL Scripts, Stored Procedures And Triggers

[Skittle]

How do you guys control putting live changes to stored procedures and triggers in SQL Server? Auditors are becoming a pain about it - SOX is on the horizon.

It seems a very on/off thing to me.

OK so you can make a backup before you change anything but how do you solve the following:-

- Keep a copy of the previous version of a stored
procedure or trigger trigger code?

- Log changes made to the database code?

- Log new/removed users and changed authorities?

- Report a users current effective database authorities on
request?






I have been doing it with a word document for each request showing before and after code.

I also keep a simple Excel sheet of added/removed users with
permission changes.

Dazed and confused

 

[SQLDenis]

>>I have been doing it with a word document for each request showing before and after code.

use a source control system like Subversion or VSS



>>Report a users current effective database authorities on
request?


create a proc that will give you this result


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

We use source save and Subversion (in the process of changing over from one to the other). Periodically, the dbas delete any objects that don't match what is in source control to keep everybody honest about using it.

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

 

[gcoast]

SQLSister,

Just curious how Subversion is working for you? We are in need of a version control system and have been exploring the options of VSS and Subversion.


Thanks much,

gcoast

 

[SQLDenis]

Subversion works fine for us
get tortoise installed to make your life easier

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[onpnt]

>>control putting live changes to stored procedures and triggers in SQL Server?

Only the DBA(s) are allowed to make these changes and they are documented to the SOX controls.
As long as your controls are well written and you use as mentioend VSS or subversion (version control software) you will be fine with the auditors.

As far as users etc.. tracking look into idera.com and the tools they provide or similar thrid party tools.

Or you can write triggers on the databases to log everything if you're on 2005 (DDL triggers)


_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[SQLSister]

People seem to like Subversion better than Source Save once they get used to it.



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