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?
Is the user account you're running the BACKUP DATABASE query under part of the db_backupoperator group?