Technical Article

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

Rate

Share

Share

Rate