Tracking the changes done to the db tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Tracking the changes done to the db tables

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-12 : 13:44:14

Recently we had a issue when one of the developer renamed columns in a table and as a result a scheduled report failed to run because of that. How can I track any such changes done in production like renaming columns, altering stored procs, creating tables etc. Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-12 : 13:51:58

in sql server 2000 you can't. in 2005 you can.

the only way i know of to acomplish this on SS 2000 is to revoke change permissions. this also applies to 2005 of course

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-12 : 16:32:37

Are there any other ways in sql 2000? Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 16:37:34

Why does the developer have high-level of permissions in production? That is not common practice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-12 : 17:03:29

quote:
Originally posted by tkizer

Why does the developer have high-level of permissions in production? That is not common practice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I took away the permissions, but I just need to track such things going forward. Thanks for the help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-12 : 17:12:24

Only way that I can think of to do it in SQL Server 2000 is to have a constant trace running.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-12 : 23:11:59

Idera has free tool to monitor schema changes.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-13 : 05:03:24

you also might want to look into built in C2 auditing...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-13 : 05:04:17

rmiao:
could you provide a link please?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-13 : 05:10:37

Spirit

Google is your freind:

http://www.idera.com/Products/SQLchange/default.aspx

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-13 : 05:13:00

and that's free?? so with what do they make money?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-12-13 : 06:31:36

Some of their other products aren't. I'm sure that I have seen one of their advertising banners on this site before too.

Duane.

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-13 : 12:25:59

quote:
Originally posted by tkizer

Only way that I can think of to do it in SQL Server 2000 is to have a constant trace running.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Is it sql profiler? Thanks

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-13 : 12:28:14

quote:
Originally posted by spirit1

you also might want to look into built in C2 auditing...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

What is C2 auditing? Thanks.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-13 : 12:51:16

C2 auditing:
http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2007-12-13 : 12:59:35

quote:
Originally posted by spirit1

C2 auditing:
http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

That's a very good article, thanks!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-13 : 23:28:33

>> and that's free?? so with what do they make money?

It can monitor one instance only, and doesn't send out alert messages.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-13 : 23:30:38

>> could you provide a link please?

Link is on top left of this page.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources