Track changes to SQL Server 2000 and 2005 with one simple utility
Michelle Gutzait, Contributor
The problem:
I am the systems administrator and SQL Server DBA in my organization. We're often required to
immediately add databases, logins or jobs to the different SQL Server environments, especially in
the development and testing environments. Since I can't allow myself to become a bottleneck for
such changes, I had to delegate permissions for these SQL Server changes to the project managers.
That said, I'd like to be notified regarding these changes. How can I achieve this goal without
spending time on designing and developing a specific application? The problem is, I have both SQL
Server 2000 and 2005 all over the place. I need something that will work for both environments.
The solution:
There are a few options to achieve my goal in SQL Server
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
Dig Deeper
-
People who read this also read...
-
This was first published in April 2008
2005, such as DDL triggers and event
notifications. But SQL Server 2000 is more limited. To achieve my goal in both environments, I had
to program a small utility that could run once a day or a few times a day, monitor changes in the
respective system tables and send a message if changes are, in fact, detected. In order for that to
work, SQL Mail (SQL 2000) or Database Mail (SQL 2005) must be configured already. I created a
sp_send_dbmail stored procedure on my SQL 2000 instances that simply executes the xp_smtp_sendmail
stored procedure.
Here are the example scripts I use in order to receive notification when new objects are created
in my SQL Server instance. You can use these same scripts for modifications and deletions of the
corresponding server objects:
- New job:
A trigger can be created on the sysjobs table in the msdb database, so you don't need a daily
job for that matter:
(Click on image to download script)
 |
| Tips on monitoring SQL Server: |
|
|
|
|
|
|
Note: Triggers on system tables, such as sysjobs, are not always
recommended since there is a danger that when the SQL Server version is upgraded, these triggers
will be dropped or will stop functioning. Be sure to document these triggers and keep their source
code for your successors and team members.
For the other system tables in the master database (such as syslogins and sysdatabases), it's
impossible to create triggers, so you should take another approach.
- New login:
I create a table in tempdb holding the login names from the last time the job was executed.
Every time it executes, I compare the contents of this table with syslogins and send a message with
the new logins, if there are any. If the content of the table changed, I update the one in tempdb
accordingly. It is better to create the table in a DBA database instead of in tempdb -- this way
the table will not be dropped when SQL Server service is restarted for any reason. The following
script should be executed every period of time (such as once a day):
(Click on image to download script)
- New database:
I do the same as I did with the new logins:
(Click on image to download script)
A more realistic solution:
You can apply the sample code – except for the trigger that will execute anyway – when a new job is
added from a central location by executing it as remote stored procedures.
For example, create the following on server_A, server_B and server_C:
(Click on image to download script)
And run the following from server D (which is a central server) using Linked Servers:
exec Server_A.master.dbo.spDBA_NewDatabaseNotification
exec Server_B.master.dbo.spDBA_NewDatabaseNotification
exec Server_C.master.dbo.spDBA_NewDatabaseNotification
exec Server_D.master.dbo.spDBA_NewDatabaseNotification
Conclusion:
A simple way to receive notification about changes in your SQL Server objects, such as logins,
databases and jobs, when you have both SQL Server 2000 and SQL Server 2005 is to monitor the delta
of the system tables that hold the object's information or have a trigger on the object's table
when it's possible.
ABOUT THE AUTHOR
Michelle Gutzait works as a senior database consultant for ITERGY International Inc., an IT consulting firm
specializing in the design, implementation, security and support of Microsoft products in the
enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and
database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills
include SQL Server infrastructure design, database design, performance tuning, security, high
availability, VLDBs, replication, T-SQL/packages coding, and more.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation