To Do -Track changes to SQL Server 2000 and 2005 with one simple utility

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 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:

USE [msdb]
GO
create trigger [dbo].[trg_DBA_sysjobs]
on [dbo].[sysjobs] for insert
as
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'michelle.gutzait@Adventure-Works.com',
@body=N'A job has been created.'
End


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):

if not exists (select 1 from tempdb..sysobjects
where type = 'U' and name = 'DBA_syslogins')
create table tempdb..DBA_syslogins
(name sysname not null primary key)
go
declare @body varchar(8000)
set @body = 'New logins: '

-- If it's not the first time it executes:
if exists (select 1 from tempdb..DBA_syslogins)
begin
-- If there are any new logins:
if exists (select 1 from master..syslogins
where name not in
(select name from tempdb..DBA_syslogins))
begin
select @body = @body + name + ','
from master..syslogins
where name not in (select name from tempdb..DBA_syslogins)

-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)

-- Send the message to the DBA with new logins:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'michelle.gutzait@Adventure-Works.com',
@body=N'New logins have been created.'
end
end

-- Modify the table’s content anyway
-- (since we’re not checking for deleted logins):
truncate table tempdb..DBA_syslogins
insert into tempdb..DBA_syslogins (name)
select name from master..syslogins

--------------------------------------------------------
New database:
============
I do the same as I did with the new logins:
------------------------------------------
if not exists (select 1 from tempdb..sysobjects where type = 'U' and name = 'DBA_sysdatabases')
create table tempdb..DBA_sysdatabases
(name sysname not null primary key)
go
declare @body varchar(8000)
set @body = 'New databases: '

-- If it's not the first time it executes:
if exists (select 1 from tempdb..DBA_sysdatabases)
begin
-- If there are any new logins:
if exists (select 1 from master..sysdatabases
where name not in
(select name from tempdb..DBA_sysdatabases))
begin
select @body = @body + name + ','
from master..sysdatabases
where name not in
(select name from tempdb..DBA_sysdatabases)

-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)

-- Send the message to the DBA with new databases:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'michelle.gutzait@Adventure-Works.com',
@body=N'New databases have been created.'

end
end

-- Modify the table’s content anyway
-- (since we’re not checking for deleted databases):
truncate table tempdb..DBA_sysdatabases
insert into tempdb..DBA_sysdatabases (name)
select name from master..sysdatabases

--------------------------------------------------------------
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:


--------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spDBA_NewDatabaseNotification')
BEGIN DROP Procedure spDBA_NewDatabaseNotification
END
GO

Create proc spDBA_NewDatabaseNotification
As

if not exists (select 1 from tempdb..sysobjects where type = 'U' and name = 'DBA_sysdatabases')
create table tempdb..DBA_sysdatabases
(name sysname not null primary key)
declare @body varchar(8000)
set @body = 'New databases: '

-- If it's not the first time it executes:
if exists (select 1 from tempdb..DBA_sysdatabases)
begin
-- If there are any new logins:
if exists (select 1 from master..sysdatabases
where name not in
(select name from tempdb..DBA_sysdatabases))
begin
select @body = @body + name + ','
from master..sysdatabases
where name not in
(select name from tempdb..DBA_sysdatabases)

-- Omit the last comma:
select @body = substring(@body,1,LEN(@body)-1)



end
end

-- Modify the table’s content anyway
-- (since we’re not checking for deleted databases):
truncate table tempdb..DBA_sysdatabases
insert into tempdb..DBA_sysdatabases (name)
select name from master..sysdatabases
-------------------------------------------------
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.