How to monitor data structure change in 2000

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

General SQL Server Forums

New to SQL Server Programming

How to monitor data structure change in 2000

Author

Topic

shezad
Starting Member

32 Posts

Posted - 2010-10-10 : 03:48:07

Hi,

I have one question I have doubt that someone is trying to access
our sql server 2000 server , because few days I have found some permission changes on particular table. but I cant trace it anywhere
like event log or in audit.

is there any utility which will continue monitor data structure
change and notify you if anything occurred.

Regards
Shezad

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-10 : 03:54:22

you can monitor on daily basis changes on table schema.
e.g.:

select * from information_schema.columns where table_name = 'MyTable'

you can also use notifications: http://www.mssqltips.com/tip.asp?tip=2121&home

best

shezad
Starting Member

32 Posts

Posted - 2010-10-10 : 04:39:45

thanks for the response, there are approx 800 tables are there
it is not possible to check them on daily basis any other software

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-10-10 : 05:44:10

no problem if you have 800 tables.
what you should do is on daily basis (or hourly basis or weekly basis) is to log your schema of 800 tables and when you need to check for changes you take last snapshot of your table schema from your log and compare it with current schema.
based on that you will find what was changed.


select *
into my_log_schema
from information_Schema.columns

after a week, lets assume you have added one table:

create table testTable (id int)

and then you want to see if anything e.g. has been added:


select * from information_schema.columns as inf
where not exists (
select * from my_log_schema as mlog
where
	 mlog.table_name = inf.table_name
and mlog.column_name = inf.column_name
	)

shezad
Starting Member

32 Posts

Posted - 2010-10-10 : 06:08:13

thanks it is nice it is possible to add hostname in this once so we can know
actually who did this

MyDiab
Starting Member

2 Posts

Posted - 2011-03-20 : 15:43:58

You note that you had permission changes and that you were looking for a utility: Here is a good product, DIAB at DIABSQLSoftware.com will monitor your permissions and inform you of any change from the role level down to the object level. I use it for SOX compliance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources