June 30, 2003 at 3:42 pm
Hi All,
Can we have a trigger on the Database Schema?
I am trying to see a way to get notified whenever a database/table/view structure OR SP changes.
.
June 30, 2003 at 4:35 pm
Change to the schema affect the system tables which, unfortunately, cannot have triggers on them.
You can schedule a 24-hour trace on the server, looking for statements starting with CREATE, DROP or ALTER. But drawbacks with this approach is that you'll pick up creation of temporary objects too. It's also all too easy for someone who suspects they're being "watched" to structure their SQL statements so they're not captured.
Another option is monitor crdate in sysobjects to tell you what has been created recently. However, the ALTER statement doesn't update this column. The ALTER does however result in the "internal use" column BASE_SCHEMA_VER being incremented.
Other contributors may have ideas/solutions.
Cheers,
- Mark
June 30, 2003 at 11:56 pm
"It's also all too easy for someone who suspects they're being "watched" to structure their SQL statements so they're not captured." -- do you mean that, people can do this with a procedure or is there any other way other than SQL query?
July 1, 2003 at 12:41 am
If you use Profiler, you can do several things:
1) Filter out TempDB. This will eliminate any temporary table creation.
2) You can parse T-SQL statements for CREATE, DROP, and ALTER, bur mccork is right... certain stored procedures are considered sensitive by SQL Server and it will intentionally mask the T-SQL statement if it's passed as part of a batch. However, if you're auditing individual statements, you should be able to capture them.
3) You can also capture object creation and deletion with specific events for Object:Created and Object:Deleted.
Triggers on system tables are not guaranteed to fire. As a result, you don't want to consider such a solution.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
July 1, 2003 at 6:18 am
OK - here is a tip which Microsoft wouldn't approve of. If you enable updates top system tables, then update the sysobjects entries for the system tables you want to put triggers on. You need to set the xtype column to 'U' rather than 'S'. It will then be possible to place a trigger on the tables concerned (syscolumns, sysobjects...) - BUT be very careful as putting triggers on could hit performance really badly.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 1, 2003 at 9:39 am
Here is another tip I would warmly recommend (I am in no way associated with Lumigent but I have a strong liking for this product) as it is very easy-to-use tool. And very good price for it too (free).
http://www.lumigent.com/downloads/schemaalert/
Hope it helps 🙂
/Hans
July 1, 2003 at 4:04 pm
You could use Bill Wunder's DDL Archive Utility for SQL Server. Have it turned on and then you can check Visual Source Safe for ANY change. I do it.. works well. It doesn't give me an immediate notification of a change, but it does do a good job of picking up changes to *anything*.
July 1, 2003 at 4:20 pm
Bill Wunder's DDL Archive Utility is returning an error with "URL not found".
Does anyone has any local copy of this tool???
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply