/* create database admin exec msdb.dbo.sp_add_category 'Job', 'Local', 'Change Control' */ -- must have dbo authority set nocount on use Admin go -- clean up any old tables if (objectproperty(object_id('Admin.dbo.ActivityLog'),'IsUserTable') = 1) drop table dbo.ActivityLog if (objectproperty(object_id('Admin.dbo.ProjectNode'),'IsUserTable') = 1) drop table Admin.dbo.ProjectNode if (objectproperty(object_id('Admin.dbo.SqlDatabase'),'IsUserTable') = 1) drop table dbo.SqlDatabase if (objectproperty(object_id('Admin.dbo.SqlServer'),'IsUserTable') = 1) drop table Admin.dbo.SqlServer if (objectproperty(object_id('Admin.dbo.Environment'),'IsUserTable') = 1) drop table Admin.dbo.Environment if (objectproperty(object_id('Admin.dbo.Project'),'IsUserTable') = 1) drop table Admin.dbo.Project go -- create tables create table Admin.dbo.Project (id int identity(1,1) not null, projectName varchar(30) not null, vssShare varchar(255) null, vssHierarchyRoot varchar(255) null, createDate datetime not null constraint dft_Project__createDate default current_timestamp, createUser varchar(50) not null constraint dft_Project__createUser default system_user, lastUpdateDate datetime null, lastUpdateUser varchar(50) null, constraint pkn_Project__id primary key nonclustered (id), constraint ukn_Project unique nonclustered (projectName), constraint chk_Project__vssShare check (substring(vssShare,1,2) = '\\' and substring(vssShare,datalength(vssShare),1) = '\' ), constraint chk_Project__vssHierarchyRoot check (substring(vssHierarchyRoot,1,2) = '$/' and substring(vssHierarchyRoot,datalength(vssHierarchyRoot),1) = '/'), constraint chk_Project__createUser check (charindex('\',createUser) > 0), constraint chk_Project__lastUpdateUser check (charindex('\',lastUpdateUser) > 0)) create table Admin.dbo.Environment (id int identity(1,1) not null, parentEnvironment_id int, environmentName varchar(30) not null, environmentDescription varchar(200) not null constraint dft_Environment__environmentDescription default ('not provided'), createDate datetime not null constraint dft_Environment__createDate default current_timestamp, createUser varchar(50)not null constraint dft_Environment__createUser default system_user, lastUpdateDate datetime null, lastUpdateUser varchar(50) null, constraint pkn_Environment__id primary key nonclustered (id), constraint ukn_Environment__environmentName unique nonclustered (environmentName), constraint fk_Environment__parentEnvironment_id__to__Environment__id foreign key (parentEnvironment_id) references Environment (id), constraint chk_Environment__environmentName check (charindex(' ',environmentName) = 0), constraint chk_Environment__createUser check (charindex('\',createUser) > 0), constraint chk_Environment__lastUpdateUser check (charindex('\',lastUpdateUser) > 0)) -- instance server name for SQL 2000 named instance compatability create table Admin.dbo.SqlServer (id int identity(1,1) not null, serverName varchar(30) not null, instanceName varchar(30) not null, workingFolderRoot varchar(100) not null, createDate datetime not null constraint dft_SqlServer__createDate default current_timestamp, createUser varchar(50) not null constraint dft_SqlServer__createUser default system_user, lastUpdateDate datetime null, lastUpdateUser varchar(50) null, constraint pkn_SqlServer__id primary key nonclustered (id), constraint ukn_SqlServer unique nonclustered (serverName, instanceName), constraint chk_SqlServer__workingFolderRoot check (substring(workingFolderRoot,datalength(workingFolderRoot),1) = '\'), constraint chk_SqlServer__createUser check (charindex('\',createUser) > 0), constraint chk_SqlServer__lastUpdateUser check (charindex('\',lastUpdateUser) > 0)) create table Admin.dbo.SqlDatabase (id int identity(1,1) not null, databaseName varchar(30) not null, createDate datetime not null constraint dft_SqlDatabase__createDate default current_timestamp, createUser varchar(50) not null constraint dft_SqlDatabase__createUser default system_user, lastUpdateDate datetime null, lastUpdateUser varchar(50) null, constraint pkn_SqlDatabase__id primary key nonclustered (id), constraint ukn_SqlDatabase__databaseName unique nonclustered (databaseName), constraint chk_sqlDatabase__createUser check (charindex('\',createUser) > 0), constraint chk_sqlDatabase__lastUpdateUser check (charindex('\',lastUpdateUser) > 0)) create table Admin.dbo.ProjectNode (id int identity(1,1) not null, project_id int not null, environment_id int not null, sqlServer_id int not null, sqlDatabase_id int not null, vssUserName varchar(30) not null constraint dft_ProjectNode__vssUserName default substring(system_user,charindex('\', system_user) + 1, datalength(system_user)), isActive bit not null constraint dft_ProjectNode__isActive default (1), activeDate datetime not null constraint dft_ProjectNode__activeDate default current_timestamp, endDate datetime null, createDate datetime not null constraint dft_ProjectNode__createDate default current_timestamp, createUser varchar(50) not null constraint dft_ProjectNode__createUser default system_user, lastUpdateDate datetime null, lastUpdateUser varchar(50) null, constraint pkn_ProjectNode__id primary key nonclustered (id), constraint fk_ProjectNode__project_id__to__Project__id foreign key (project_id) references Project (id), constraint fk_ProjectNode__environment_id__to__Environment__id foreign key (environment_id) references Environment (id), constraint fk_ProjectNode__sqlServer_id__to__SqlServer__id foreign key (sqlServer_id) references sqlServer (id), constraint fk_ProjectNode__sqlDatabase_id__to__SqlDatabase__id foreign key (sqlDatabase_id) references SqlDatabase (id), constraint ukn_ProjectNode__sqlServer_id__sqlDatabase_id unique nonclustered (sqlServer_id, sqlDatabase_id), constraint chk_ProjectNode__isActive check (isActive in(0,1)), constraint chk_ProjectNode__createUser check (charindex('\',createUser) > 0), constraint chk_ProjectNode__lastUpdateUser check (charindex('\',lastUpdateUser) > 0)) create table Admin.dbo.ActivityLog (id int identity(1,1) not null, activity varchar(2000) not null, activityDate datetime not null constraint dft_ActivityLog__activityDate default current_timestamp, activityUser varchar(50) not null constraint dft_ActivityLog__activityUser default system_user, constraint pkn_ActivityLog__id primary key nonclustered (id), constraint chk_ActivityLog__activityUser check (charindex('\',activityUser) > 0)) go -- create an index on ProjectNode database_id -- don't really need it, just want an index for scripting to verify the dmo procedures create nonclustered index ixc_ProjectNode__sqlDatabase_id on ProjectNode(sqlDatabase_id) go --create triggers create trigger dbo.itr_Project on Admin.dbo.Project for insert as if @@rowcount > 0 begin -- audit the insert insert Admin.dbo.ActivityLog (activity) select 'Project ' + i.projectName + ' inserted' from inserted i inner join Admin.dbo.Project p on i.id = p.id end go create trigger dbo.utr_Project on Admin.dbo.Project for update as if @@rowcount > 0 begin update Admin.dbo.Project set lastUpdateDate = current_timestamp, lastUpdateUser = system_user from inserted i inner join Admin.dbo.Project p on i.id = p.id -- audit the update insert Admin.dbo.ActivityLog (activity) select 'Project updated:' + case when d.projectName <> i.projectName then ' projectName: ' + d.projectName + ' --> ' + i.projectName else ' ' end + case when d.vssHierarchyRoot <> i.vssHierarchyRoot then ' vssHierarchyRoot: ' + d.vssHierarchyRoot + ' --> ' + i.vssHierarchyRoot else ' ' end + case when d.vssShare <> i.vssShare then ' vssShare: ' + d.vssShare + ' --> ' + i.vssShare else ' ' end + ' ' from deleted d inner join inserted i on d.id = i.id inner join Admin.dbo.Project p on d.id = p.id end go create trigger dbo.dtr_Project on Admin.dbo.Project for delete as if @@rowcount > 0 begin -- audit the delete insert Admin.dbo.ActivityLog (activity) select 'Project ' + d.projectName + ' deleted' from deleted d inner join Admin.dbo.Project p on d.id = p.id end go create trigger dbo.itr_Environment on Admin.dbo.Environment for insert as if @@rowcount > 0 begin -- audit insert Admin.dbo.ActivityLog (activity) select 'Environment ' + i.environmentName + ' inserted' from inserted i inner join Admin.dbo.Environment e on i.id = e.id end go create trigger dbo.utr_Environment on Admin.dbo.Environment for update as if @@rowcount > 0 begin update Admin.dbo.Environment set lastUpdateDate = current_timestamp, lastUpdateUser = system_user from inserted i inner join Admin.dbo.Environment e on i.id = e.id -- audit insert Admin.dbo.ActivityLog (activity) select 'Environment ' + i.environmentName + ' updated' from inserted i inner join Admin.dbo.Environment e on i.id = e.id end go create trigger dbo.dtr_Environment on Admin.dbo.Environment for delete as if @@rowcount > 0 begin -- audit insert Admin.dbo.ActivityLog (activity) select 'Environment ' + d.environmentName + ' deleted' from deleted d inner join Admin.dbo.Environment e on d.id = e.id end go create trigger dbo.itr_SqlServer on Admin.dbo.SqlServer for insert as if @@rowcount > 0 begin -- audit insert Admin.dbo.ActivityLog (activity) select 'SqlServer ' + i.serverName + '\' + i.instanceName + ' inserted' from inserted i inner join Admin.dbo.SqlServer s on i.id = s.id end go create trigger dbo.utr_SqlServer on Admin.dbo.SqlServer for update as if @@rowcount > 0 begin update Admin.dbo.SqlServer set lastUpdateDate = current_timestamp, lastUpdateUser = system_user from inserted i inner join Admin.dbo.SqlServer ss on i.id = ss.id -- audit insert Admin.dbo.ActivityLog (activity) select 'SqlServer ' + i.serverName + '\' + i.instanceName + ' updated' from inserted i inner join Admin.dbo.SqlServer s on i.id = s.id end go create trigger dbo.dr_SqlServer on Admin.dbo.SqlServer for delete as if @@rowcount > 0 begin -- audit insert Admin.dbo.ActivityLog (activity) select 'SqlServer ' + d.serverName + '\' + d.instanceName + ' deleted' from deleted d inner join Admin.dbo.SqlServer s on d.id = s.id end go create trigger dbo.itr_SqlDatabase on Admin.dbo.SqlDatabase for insert as if @@rowcount > 0 begin -- audit insert Admin.dbo.ActivityLog (activity) select 'SqlDatabase ' + i.databaseName + ' inserted' from inserted i inner join Admin.dbo.SqlDatabase d on i.id = d.id end go create trigger dbo.utr_SqlDatabase on Admin.dbo.SqlDatabase for update as if @@rowcount > 0 begin update Admin.dbo.SqlDatabase set lastUpdateDate = current_timestamp, lastUpdateUser = system_user from inserted i inner join Admin.dbo.SqlDatabase db on i.id = db.id -- audit insert Admin.dbo.ActivityLog (activity) select 'SqlDatabase ' + d.databaseName + ' updated' from deleted d inner join Admin.dbo.SqlDatabase db on d.id = db.id end go create trigger dbo.dtr_SqlDatabase on Admin.dbo.SqlDatabase for delete as if @@rowcount > 0 begin -- audit the insert insert Admin.dbo.ActivityLog (activity) select 'SqlDatabase ' + d.databaseName + ' deleted' from deleted d inner join Admin.dbo.SqlDatabase db on d.id = db.id end go create trigger dbo.itr_ProjectNode on Admin.dbo.ProjectNode for insert as if @@rowcount > 0 begin -- audit the insert insert Admin.dbo.ActivityLog (activity) select 'ProjectNode ' + cast(n.id as varchar(10))+ ' inserted' from inserted i inner join Admin.dbo.ProjectNode n on i.id = n.id end go create trigger dbo.utr_ProjectNode on Admin.dbo.ProjectNode for update as if @@rowcount > 0 begin update Admin.dbo.ProjectNode set lastUpdateDate = current_timestamp, lastUpdateUser = system_user, activeDate = case when (i.isActive = 1 and d.isActive = 0) then current_timestamp else d.activeDate end, endDate = case when (i.isActive = 0 and d.isActive = 1) then current_timestamp else d.endDate end from inserted i inner join deleted d on i.id = d.id inner join Admin.dbo.ProjectNode db on i.id = db.id -- audit the update insert Admin.dbo.ActivityLog (activity) select 'ProjectNode updated: id = ' + cast(n.id as varchar(10)) + ' old value --> new value environment_id: ' + cast(d.environment_id as varchar(10)) + ' --> ' + cast(i.environment_id as varchar(10)) + ' sqlServer_id: ' + cast(d.sqlServer_id as varchar(10)) + ' --> ' + cast(i.sqlServer_id as varchar(10)) + ' sqlDatabase_id: ' + cast(d.sqlDatabase_id as varchar(10)) + ' --> ' + cast(i.sqlDatabase_id as varchar(10)) + ' vssUserName: ' + d.vssUserName + ' --> ' + i.vssUserName +' isActive: ' + cast(d.isActive as char(1)) + ' --> ' + cast(i.isActive as char(1)) + ' activeDate: ' + cast(d.activeDate as varchar(21)) + ' --> ' + cast(i.activeDate as varchar(21)) + ' endDate: ' + isnull(cast(d.endDate as varchar(21)),'null') + ' --> ' + isnull(cast(i.endDate as varchar(21)),'null') + ' createDate: ' + cast(d.createDate as varchar(21)) + ' --> ' + cast(i.createDate as varchar(21)) + ' createUser: ' + d.createUser + ' --> ' + i.createUser + ' lastUpdateDate: ' + isnull(cast(d.lastUpdateDate as varchar(21)),'null') + ' --> ' + isnull(cast(i.lastUpdateDate as varchar(21)),'null') + ' lastUpdateUser: ' + isnull(d.lastUpdateUser,'null') + ' --> ' + isnull(i.lastUpdateUser,'null') from deleted d inner join inserted i on d.id = i.id inner join Admin.dbo.ProjectNode n on d.id = n.id end go create trigger dbo.dtr_ProjectNode on Admin.dbo.ProjectNode for delete as if @@rowcount > 0 begin -- audit the insert insert Admin.dbo.ActivityLog (activity) select 'ProjectNode ' + cast(n.id as varchar(10)) + ' deleted' from deleted d inner join Admin.dbo.ProjectNode n on d.id = n.id end go /* no instead of triggers in 7 -- log updates not permitted create trigger dbo.utr_ActivityLog on Admin.dbo.ActivityLog instead of update as if @@rowcount > 0 begin insert Admin.dbo.ActivityLog (activity) select 'Attempt to update activityLog activity_id = ' + cast(i.id as varchar(10)) + ' to activity = ' + i.activity + ' activityDate = ' + cast(i.ActivityDate as varchar(21)) + ' activityUser = ' + i.ActivityUser from deleted d inner join inserted i on d.id = i.id inner join Admin.dbo.ActivityLog a on d.id = a.id end -- log deletes not permitted create trigger dbo.dtr_ActivityLog on Admin.dbo.ActivityLog instead of delete as if @@rowcount > 0 begin insert Admin.dbo.ActivityLog (activity) select 'Attempt to delete activityLog activity_id = ' + cast(d.id as varchar(10)) from deleted d inner join Admin.dbo.ActivityLog a on d.id = a.id end */ -- log updates not permitted create trigger dbo.utr_ActivityLog on Admin.dbo.ActivityLog for update as if @@rowcount > 0 begin insert Admin.dbo.ActivityLog (activity) select 'Attempt to update activityLog activity_id = ' + cast(i.id as varchar(10)) + ' to activity = ' + i.activity + ' activityDate = ' + cast(i.ActivityDate as varchar(21)) + ' activityUser = ' + i.ActivityUser from deleted d inner join inserted i on d.id = i.id inner join Admin.dbo.ActivityLog a on d.id = a.id -- this is not needed in the instead of triggger update Admin.dbo.ActivityLog set activity = d.activity, activityDate = d.activityDate, activityUSer = d.activityUser from deleted d inner join inserted i on d.id = i.id inner join Admin.dbo.ActivityLog a on d.id = a.id end go -- log deletes not permitted create trigger dbo.dtr_ActivityLog on Admin.dbo.ActivityLog for delete as if @@rowcount > 0 begin insert Admin.dbo.ActivityLog (activity) select 'Attempt to delete activityLog activity_id = ' + cast(d.id as varchar(10)) from deleted d inner join Admin.dbo.ActivityLog a on d.id = a.id -- this is not needed in the instead of triggger update Admin.dbo.ActivityLog set activity = d.activity, activityDate = d.activityDate, activityUSer = d.activityUser from deleted d inner join inserted i on d.id = i.id inner join Admin.dbo.ActivityLog a on d.id = a.id end go -- functions /*no functions in 7 if objectproperty(object_id('dbo.thisSqlServer_id'),'IsInlineFunction') is not null drop function dbo.thisSqlServer_id create function thisSqlServer_id () returns int as begin -- get current system's id from table SqlServer declare @thisSqlServer_id int select @thisSqlServer_id = (select id from Admin.dbo.SqlServer where serverName = reverse(substring(reverse(@@servername), charindex('\',reverse(@@servername)) + 1, datalength(reverse(@@servername)))) and instanceName = substring(@@servername, charindex('\',@@servername) + 1, datalength(@@servername))) return (@thisSqlServer_id) end if objectproperty(object_id('dbo.binToHex'),'IsInlineFunction') is not null drop function dbo.binToHex create function dbo.binToHex (@binaryValue varbinary(255)) returns varchar(255) as begin declare @binToHex varchar(255), @curPosition int, @hexString char(16), @tempInt int, @firstInt int, @secondInt int set @binToHex = '0x' set @curPosition = 1 set @hexString = '0123456789abcdef' while (@curPosition <= datalength(@binaryValue)) begin select @tempInt = cast(substring(@binaryValue, @curPosition, 1) as int) select @firstint = floor(@tempInt/16) select @secondint = @tempInt - (@firstInt * 16) select @binToHex = @binToHex + substring(@hexString, @firstInt + 1, 1) + substring(@hexString, @secondInt + 1, 1) select @curPosition = @curPosition + 1 end return (@binToHex) end */ go