1

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?

  • 1
    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 – Jonathan Fite Feb 22 '17 at 13:13
  • 3
    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. – Scott Hodgin Feb 22 '17 at 13:18
  • 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. – Caynadian Feb 22 '17 at 16:12
  • 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? – Shaulinator Feb 22 '17 at 18:20
  • 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. – Caynadian Feb 23 '17 at 11:26
0

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.

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.