Get Notified When Accounts Are Changed
Perfect for finding developers that are changing your database accouts. This procedure can check the sysusers table for each database and\or the master..syslogins table for account changes. Gives the option of email notification, logging to a table or both. Update the set statements at the begining of the procedure to configure it however you like. It will create any tables it requires for you. The procedure accepts on parameter for the number of days back to report a change on. I would suggest setting this up as a scheduled procedure, so if you run the report daily pass in a 1, weekly a 7, etc. If there is an account change detected and you use the CreateBackupTable option you can then compare the account records before and after the change to find out what the user changed. If you want to create a new copy of the backup tables, just delete them and they will be recreated the next time it runs.
create procedure AccountChangeCheck (@iDaysDiff int = 1)
as
declare @sSql varchar(8000)
declare @sDBName varchar(128)
declare @sUserName varchar(128)
declare @sLoggingDB varchar(128)
declare @sLoggingTable varchar(128)
declare @sLoggingDBTable varchar(256)
declare @sEmailAddress varchar(128)
declare @iLoggingEnabled int
declare @iEmailEnabled int
declare @iCreateBackupTable int
declare @iCheckMasterOnly int
set nocount on
-- Pass the Number of days back to check for Account changes to the procedure
set @iLoggingEnabled = 1 -- Set to 1 to enable logging a logging table will be created
-- a logging database name and table is required if you enable logging
set @iCreateBackupTable = 1-- Set to 1 if you would like to create a backup table in each database
-- each backup table will be appended with the name '_backup'
-- The backup table can be used to compare the changes that were made.
set @sLoggingDB = 'eoperationsdba'
set @sLoggingTable = 'AccountChangeLog'
set @iEmailEnabled = 1 -- Set to 1 to enable email notification
set @sEmailAddress = 'youremail@yourdomain.com'
set @iCheckMasterOnly = 0 -- Set to 1 to only check the master..syslogins table
select @sUserName = user
set @sLoggingDBTable = @sLoggingDB + '.' + @sUserName + '.' + @sLoggingTable
declare c1 cursor fast_forward for select name from master..sysdatabases
open c1
fetch next from c1 into @sDBName
while @@fetch_status = 0
begin
if @sDBName = 'master' and @iCreateBackupTable = 1
begin
select @sSql = 'if not exists (select * from ' + @sDBName + '.dbo.sysobjects where type = ''u'' and name = ''syslogins_backup'')
begin
create table ' + @sDBName + '.' + @sUserName + '.syslogins_backup (
sidvarbinary(85),
statussmallint,
createdatedatetime,
updatedatedatetime,
accdatedatetime,
totcpuint,
totioint,
spacelimitint,
timelimitint,
resultlimitint,
namenvarchar(256),
dbnamenvarchar(256),
passwordnvarchar(256),
languagenvarchar(256),
denyloginint,
hasaccessint,
isntnameint,
isntgroupint,
isntuserint,
sysadminint,
securityadminint,
serveradminint,
setupadminint,
processadminint,
diskadminint,
dbcreatorint,
bulkadminint,
loginnamenvarchar(256))
insert into ' + @sDBName + '.' + @sUserName + '.syslogins_backup select * from ' + @sDBName + '.dbo.syslogins
end' + char(13)
-- print @sSql
exec(@sSql)
end
if @iCreateBackupTable = 1
begin
select @sSql = 'if not exists (select * from ' + @sDBName + '.dbo.sysobjects where type = ''u'' and name = ''sysusers_backup'')
begin
create table ' + @sDBName + '.' + @sUserName + '.sysusers_backup (
uidsmallint,
statussmallint,
namesysname,
sidvarbinary(85),
rolesvarbinary(2048),
createdatedatetime,
updatedatedatetime,
altuidsmallint,
passwordvarbinary(256),
gidsmallint,
environvarchar(255),
hasdbaccessint,
isloginint,
inntnameint,
isntgroupint,
isntuserint,
issqluserint,
isaliasesint,
issqlroleint,
isapproleint)
insert into ' + @sDBName + '.' + @sUserName + '.sysusers_backup select * from ' + @sDBName + '.dbo.sysusers
end' + char(13)
-- print @sSql
exec(@sSql)
end
-- Begin Account Checks
if @sDBName = 'master'
begin
select @sSql = 'if exists (select * from master.dbo.syslogins where updatedate > dateadd(dd,-' + convert(varchar(5),@iDaysDiff) + ',getdate()))
begin
-- Should the change be logged?
if ' + convert(varchar(1),@iLoggingEnabled) + ' = 1 -- Log account change check
begin
if not exists(select * from ' + @sLoggingDB + '..sysobjects where type = ''u'' and name = ''' + @sLoggingTable + ''')
begin
create table ' + @sLoggingDBTable + ' (
DBNameVarchar(128),
uidsmallint,
namesysname,
createdatedatetime,
updatedatedatetime,
hasdbaccessint)
end
insert into ' + @sLoggingDBTable + ' (DBName, name, createdate, updatedate, hasdbaccess) select ''' + @sDBName + ''', name, createdate, updatedate, hasaccess from master.dbo.syslogins where updatedate > dateadd(dd,-' + convert(varchar(5),@iDaysDiff) + ',getdate())
end
if ' + convert(varchar(1),@iEmailEnabled) + ' = 1 --Send email check
begin
-- Create Message Body
declare @query varchar(2000)
select @query = ''print ''''Account Information Has Been Updated in Database: ' + @sDBname + '''''
select name, createdate, updatedate, hasaccess, sysadmin from master.dbo.syslogins where updatedate > dateadd(dd,-' + convert(varchar(5),@iDaysDiff) + ',getdate())''
exec master..xp_sendmail @recipients = ''' + @sEmailAddress + ''',
@subject = ''Database Account Changed'',
@query = @query,
@width = 1000,
@attach_results = ''true''
end
end'
-- print @sSql
exec(@sSql)
end
if @iCheckMasterOnly <> 1
begin
select @sSql = 'if exists (select * from ' + @sDBName + '.dbo.sysusers where updatedate > dateadd(dd,-' + convert(varchar(5),@iDaysDiff) + ',getdate()))
begin
-- Should the change be logged?
if ' + convert(varchar(1),@iLoggingEnabled) + ' = 1 -- Log account change check
begin
if not exists(select * from ' + @sLoggingDB + '..sysobjects where type = ''u'' and name = ''' + @sLoggingTable + ''')
begin
create table ' + @sLoggingDBTable + ' (
DBNameVarchar(128),
uidsmallint,
namesysname,
createdatedatetime,
updatedatedatetime,
hasdbaccessint)
end
insert into ' + @sLoggingDBTable + ' select ''' + @sDBName + ''', uid, name, createdate, updatedate, hasdbaccess from ' + @sDBName + '.dbo.sysusers where updatedate > dateadd(dd,-' + convert(varchar(5),@iDaysDiff) + ',getdate())
end
if ' + convert(varchar(1),@iEmailEnabled) + ' = 1 --Send email check
begin
-- Create Message Body
declare @query varchar(2000)
select @query = ''print ''''Account Information Has Been Updated in Database: ' + @sDBname + '''''
select uid, name, createdate, updatedate, hasdbaccess from ' + @sDBName + '.dbo.sysusers where updatedate > dateadd(dd,-' + convert(varchar(5),@iDaysDiff) + ',getdate())''
exec master..xp_sendmail @recipients = ''' + @sEmailAddress + ''',
@subject = ''Database Account Changed'',
@query = @query,
@width = 1000,
@attach_results = ''true''
end
end'
-- print @sSql
exec(@sSql)
end
fetch next from c1 into @sDBName
end
close c1
deallocate c1 
