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\ODBC\110\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\ODBC\110\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?
Best Answer
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.