0

I am debugging an issue that deals with SQL Server backing up the database to a directory.

The application I am running launches with Administrator rights, which is set in the app.manifest file:

<requestedExecutionLevel level="requireAdministrator" uiAccess="false" />

The application sets the initial directory to the default SQL Server backup directory:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

The user is allowed to change the backup directory, and they can type it in, so something like this is typed:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup 12

Then the program then instruct SQL to back up to the directory using the stored procedure:

BACKUP DATABASE @DatabaseName TO DISK = @FilePath WITH NAME = @BackupName

Where:

  • @FilePath is the directory the user selects
  • @BackupName is YYYYMMDDHHMMSS.bak

The call fails with:

Operating system error 5 (access is denied).

So I did some investigation on the directory rights using icacls on the command line for the default SQL Server backup directory:

icacls .\backup 

CREATOR OWNER:(OI)(CI)(IO)(F) 
NT AUTHORITY\SYSTEM:(OI)(CI)(F) 
BUILTIN\Administrators:(OI)(CI)(F) 
MIKE-2K8-1\SQLServerMSSQLUser$mike-2k8-1$MSSQLSERVER:(OI)(CI)(F)

Then again for the directory the user creates:

icacls .\backup 12

BUILTIN\Administrators:(I)(F) 
CREATOR OWNER:(I)(OI)(CI)(IO)(F) 
NT AUTHORITY\SYSTEM:(I)(OI)(CI)(F) 
BUILTIN\Administrators:(I)(OI)(CI)(IO)(F) 
BUILTIN\Users:(I)(OI)(CI)(RX) 
MIKE-2K8-1\SQLServerMSSQLUser$mike-2k8-1$MSSQLSERVER:(I)(OI)(CI)(RX)

I can see the permissions are different for the SQLServerMSSQLUser depending upon the directory... how can I change the permissions?

MrLister
  • 634
  • 7
  • 32

1 Answers1

1

Is the user account you're running the BACKUP DATABASE query under part of the db_backupoperator group?

binks
  • 1,001
  • 2
  • 10
  • 26
  • The backup succeeds for the directory C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup but fails for the very same user for this directory C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup 12 -- Nothing changes except the directory. – MrLister Sep 04 '14 at 17:23
  • ok, give this a try: icacls "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup 12" /grant:r "SQLServerMSSQLUser$mike-2k8-1$MSSQLSERVER":(OI)(CI)(I)(RX)M – binks Sep 04 '14 at 18:30