As DBAs, we often obsess over backups. When was the backup made, how big is it, how quickly can I restore, can I restore it, etc. But sometimes, we need to know who made a backup.
Shouldn’t that be me? Well, of course, it should be you. But sometimes, it ain’t.
A few months back, I was asked to troubleshoot a SQL Server where they were getting disk full alerts. After a few quick checks, I was able to see it was the backup LUN that was filling up.
When I looked in the folder, I noticed there was a FULL backup file with an unusual timestamp. Most of the backup files were all created around the same time of day, but there was one with a completely different time. Next, I looked in the job history and this matched what I saw at the file system level.
OK, so now I knew what caused the space alerts, I still didn’t know who had caused them. Time to dig into the msdb database. msdb is a system database that contained all the job history for your SQL Server. A quick query revealed to me who it was that ran the ad-hoc backup.
-- look for who ran a manual backup select top 30 s.database_name, s.user_name, s.backup_start_date, m.physical_device_name from msdb.dbo.backupset s join msdb.dbo.backupmediafamily m on s.media_set_id = m.media_set_id where s.type = 'D' and s.database_name = 'YOUR_DB_NAME_HERE' order by s.backup_start_date desc go
Who Made the Backup
Next, I wanted to know how the user had access to create the backup. Let’s look at security. The first place I typically look is, who has admin access.
-- check server role membership select l.name, -- 'People with elevated access' as 'name', l.sysadmin, l.securityadmin, l.serveradmin, l.setupadmin, l.processadmin, l.diskadmin, l.dbcreator, l.bulkadmin, l.createdate, l.updatedate --, l.* from sys.syslogins l where l.sysadmin = 1 or l.securityadmin = 1 or l.serveradmin = 1 or l.setupadmin = 1 or l.processadmin = 1 or l.diskadmin = 1 or l.dbcreator = 1 or l.bulkadmin = 1 order by l.name; go
Who has Admin Access
And there’s my new best friend. Sometimes referred to as the Extra DBA, or People Who Can Get You Fired. In this case, there were several people who had been granted admin access. Time to start pruning some permissions.
The biggest hole on any system is extra people who have admin access. I see this all the time; perhaps a manager needs to run reporting queries, or developers need to maintain and troubleshoot an application.
When deadlines are looming and people are standing over your shoulder, the simplest fix often seems to just grant the person admin access. You’ll remember to remove it later, or when the app is deployed, right?
