Alert for New Database
-
I'm looking to create an alert to let me know when anyone creates a database on one of my SQL 2000 boxes but I can;t seem to find an easy way to do it. There seems to be specific ways of doing this in SQL 2005 but not 2000. Any ideas? I could create a table with all the databases and run periodic checks of all current databases against it but I was looking for a slightly better way.
cheers
-
I really should read other posts before sticking my own up! Just read Log - Creation of objects......
-
you can also save a copy of sysdatabases to a table, and schedule a job to compare that table to the current sysdatabases to compare changes....same thing at the database level. ..sysobjects for new/changed tables.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I also needed something similar. All I did is to schedule a job that runs periodically that will get a count(*) of sysdatabases and compare it against the current count. An email is automatically sent to me if the count changes.
-
same here...in my case, developers were dropping and recreating databases all the time, just needed to be aware of it really; so i had to save the previous data and do a left join, to see if the dbid changed, and other data like that.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The easiest (and most evil) way to know when someone wants to drop or create a database in SQL 2000 is to deny drop database and create database permissions to everyone except you. Then believe me that you will know right away when someone tries to drop or create a database because they'll come yelling at you!!!
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
I definitely like Robert's approach.
I used to do this with objects by making my own DB, then storing a copy of sp_configure and sysobjects for each db. Then I'd have a job that would grab a new copy and compare it to the old one. If there were differences, it sent me a note.
-
I have never yet been at a client that didn't have at least a half-dozen people with SA priviledges, so that (locking out db create permission) wouldn't work in any shop I have been in! 🙂 Numerous shops I have consulted at have EVERYTHING/EVERY APP log in as sa!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Creation of databases is always reserved for the sa or someone in the sysadmin fixed role. Since this is a development situation as opposed to production the scenario changes a bit. Unless youir shop is super security concious all of the developers will generally be in the db_owner fixed riole for their given databases. This allows them to truncate tables and the transaction log, two very important functions in a development environment that need not be on the DBAs plate. However there is one drawback to being db_owner - your developers can drop their database. Don't ask why, but I have seen developers drop their database (without backing it up) and expected the ability to recreate the database themselves and have the DBA restore their data !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply