SQL Server 2012 and Changes to the Backup Operator Permissions

I’m off to Columbus, Ohio tomorrow for a full day of sessions on Friday for the Ohio Oracle User Group.  The wonderful Mary E. Brown and her group has set up a great venue and a fantastic schedule.  Next week, I’m off to SQL Saturday Vancouver to present on DevOps for the DBA to a lovely group of SQL Server attendees.  It’s my first time to Vancouver, British Columbia and as it’s one of the cities on our list of potential future locations to live, I’m very excited to visit.

Speaking of SQL Server-  Delphix‘s own SQL Server COE, (Center of Excellence) meets twice a month to discuss various topics surrounding our much-loved Microsoft offering.  This week, one of the topics discussed a previous change made to permissions to the Backup Operator role from SQL Server 2008R2 to SQL Server 2012. This feature, referred to as “File Share Scoping” was unique to 2008R2 clusters and no longer exists.

Now many may say, “but this is such an old version.  We’ve got SQL Server 2017, right?”  The challenge is, there are folks out there with 2008 instances and it’s good to know about these little changes that can make big impacts to your dependent products.  This change impacted products with shared backups file systems and as we know, having access to a backup can offload a lot of potential load on a system.

Now, for my product, Delphix, we are dependent on read access to backup files for the initial creation of our “golden copy” that we source everything from.  The change in SQL Server 2012 from the previous File Share Scoping in 2008R2 was only made to Microsoft Failover Clusters, to then offering access to only those with Administrator, where previously, anyone with Backup Operator role could attain access, too.

Our documentation clearly states during configuration of a Delphix engine for the validated sync, (creation of the golden copy) the customer must grant read access for the backup shares to the Delphix OS user and doesn’t state to grant Backup Operator.  As with everything, routine can spell failure, as the Backup Operator role previously offered this access with 2008R2 and it was easy to assume the configuration complete upon database level role grants.

Using Powershell from the command line, note that you can’t view the root of the shared drive with the file server role, Backup Operator in the newer release.

PS C:\Users\user> Get-SmbShareAccess -name "E$" | ft -AutoSize

Name ScopeName AccountName AccessControlType AccessRight
---- --------- ----------- ----------------- -----------
E$ USER1-SHARE BUILTIN\Administrators Allow Full
E$ * BUILTIN\Administrators Allow Full
E$ * BUILTIN\Backup Operators Allow Full
E$ * NT AUTHORITY\INTERACTIVE Allow Full

If you’d like to read more details on backup and recovery changes from SQL Server 2008R2 to 2012, check out the documentation from Microsoft here.