2

On a SQL Server 2012 SP2 instance the query

xp_cmdshell
'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'

fails with the output:

Access is denied.

Both of these return the windows account running the SQL Engine and SQL Agent:

EXEC master..xp_cmdshell 'set username' 

USERNAME=AngeliaSQL

EXEC xp_cmdshell 'whoami'

ntdomain\angeliasql

The windows account has Full Control on the \morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ and all files in the folder.

Is there another way to check permissions?

  • 1
    What is \backupfilepath\ exactly? What is the service account? (Don't dumb things down, it is important whether this is a local folder, unc path, mapped drive, etc.) – Aaron Bertrand Nov 30 '15 at 23:45
  • It's a UNC path. Hope this is more clear. – tpet Nov 30 '15 at 23:55
  • Is the same account writing the files to that location? – Aaron Dec 1 '15 at 4:45
  • If you use the runas command in a cmd.exe to impersonate angeliasql, can you execute the command then? – Tom V Dec 1 '15 at 13:44
  • Yes, it is the same account that writes the files to that location. No, executing the command when using runas command in cmd.exe does not work. It returns Access is denied. – tpet Dec 1 '15 at 16:25
2

You need to have a connection as well as permissions. SQL Server won't automatically connect to a network resource. You can do this either inside or outside of SQL Server but the connection has to be made on the machine. If there is a permission issue, then the 'net use' in the SQL query will return more info than just 'Access Denied'.

exec xp_cmdshell 'net use \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP mypassword /USER:ntdomain\angeliasql'

exec xp_cmdshell 'del \\morrison\backups\angelia\AS_ReportingServicesDW_MOLAP\ReportingServicesDW_MOLAP-20151116.abf'
  • I ran the commands above, after adding in the correct password. The first returned, "The command completed successfully." while the second still returned "Access is denied." – tpet Dec 1 '15 at 16:20
  • That means the user has permissions and the file exists. Is it marked read-only? exec xp_cmdshell 'attrib -r \\mynetworkpath\myfile.txt' – byrdzeye Dec 1 '15 at 17:15
  • That returns NULL – tpet Dec 1 '15 at 17:18
  • It should. Can you delete it now? – byrdzeye Dec 1 '15 at 17:26
  • Nope, no change. – tpet Dec 1 '15 at 17:29
2

The fix was to give the service account permissions one level up in the folder structure. Adding permissions on \morrison\backups\, when it had previously been on \morrison\backups\angelia\, allowed the job to run.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.