Unfortunately, I have a food court server with several databases for almost as many applications. Some vendors have access to their databases. Also unfortunately, some have db_owner rights because they refuse to do actual permissions on their database. db_owner rights allows them to do a backup of that database.

I use the following every night, say after 11pm, to check that day's backups and make sure no one other than those authorized made any backups. Just create a job and run the t-sql script.

The 'D' in the script is for checking only the full backups. It assumes that you have DB Mail set up on the instance.

/* ===================================================================================================================== */

/* This code checks for unauthorized backups and sends an email if any are found for the current day.

(Should be run late in the day, say after 11pm.)

If change the criteria, remember to change it in both the IF EXISTS section, and below in the email SELECT statement section.

*/

if exists

(SELECT S.database_name, S.recovery_model, S.user_name, S.backup_start_date, CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0/1024.0) AS backup_size_GB,

M.physical_device_name

FROM msdb..backupset S

JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id

WHERE S.type = 'D'

AND CONVERT(int,CONVERT(varchar(20),S.backup_start_date,112)) = CONVERT(int,CONVERT(varchar(20),GETDATE(),112))

and S.user_name NOT LIKE '%<serviceAccount>%')

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL Server Agent Mail Profile',

@recipients = 'someone@mycompany.com',--Use semicolon to separate multiple email addresses

@query = 'SELECT CONVERT(varchar(25),@@SERVERNAME) AS ServerName,

CONVERT(varchar(25),S.database_name) AS DatabaseName,

CONVERT(varchar(10),S.recovery_model) AS RecoveryModel,

CONVERT(varchar(30),S.user_name) AS UserName,

CONVERT(varchar(30),S.backup_start_date,109) AS BackupStartDate,

CONVERT(varchar(10),CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0/1024.0)) AS backup_size_GB,

CONVERT(varchar(100),M.physical_device_name) AS PhysicalDevicename

FROM msdb..backupset S

JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id

WHERE S.type = ''D''

AND CONVERT(int,CONVERT(varchar(20),S.backup_start_date,112)) = CONVERT(int,CONVERT(varchar(20),GETDATE(),112))

and S.user_name NOT LIKE ''%<serviceAccount>%''

ORDER by backup_start_date DESC' ,

@subject = '<servername> - Unauthorized Backups',

@attach_query_result_as_file = 1 ;

END