Execute Permission Denied Using SQLCMD

Question

I have a Windows Server 2012R2 with SQL Server Express 2014 install that supports a video app. I am trying to create a backup solution for it using the Task Scheduler to do full and incremental backups. I have the task scheduler executing a batch file that uses SQLCMD to run a stored procedure that does the backup. This is the batch file:

@Echo Off
"C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC110円\Tools\Binn\SqlCmd.exe" -S SERVER\INSTANCE -i "C:\Scripts\FullBackup.sql"

The SQL that it runs (in FullBackup.sql) is as follows:

exec spBackupDatabases '\\BackupServer\Backup\Database\Server\', 'F';

The spbackupDatabases stored procedure is created in Master. When I run this stored procedure with SSMS logged in as myself (a local administrator) it works fine. When I try and run the same thing, still logged in as myself, from the batch file I get the following error:

C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC110円\Tools\Binn>\scripts\FullBackup.cmd
Msg 229, Level 14, State 5, Server SERVER\INSTANCE, Procedure spBackupDatabases, Line 1
The EXECUTE permission was denied on the object 'spBackupDatabases', database 'master', schema 'dbo'.

There is nothing in the SQL Server Logs (even with failed and successful logins turned on) so I'm not even sure what user is being used. What might I be doing wrong?

• Jonathan Fite: Have you considered just using Ola Hallengren's database maintenance solution? Lots of organizations use it and it has an active community around it. Free! ola.hallengren.com
• Scott Hodgin - Retired: When you say, "you are running the bat file", are you double-clicking the bat file and it's failing or are you running the bat file from Task Scheduler and it's failing? What login is Task Scheduler running your task as? You might want to create a local user account on that server and grant that user the correct permissions to do what you want - then make sure Task Scheduler is running as THAT user.
• Caynadian: No, for testing right now I'm just running the batch file from the command line under my own account. Once I get that running I will figure out how to get it working properly from the Task Scheduler.
• Shaulinator: The login you are using in SQL, is it a windows or SQL login? Is that the same login you are using when you run the bat manually?
• Caynadian: It's a Windows login and it is the same one I'm using for both the batch file and running the stored procedure directly in SSMS.

Answer 1

In the end, I had to give myself explicit execute permission on the database. Even though I was the owner of the database, a sysadmin for the database, and administrator on the server, etc. - SQLCMD would only work if I had the execute permission.