0

I'm trying to setup automated backups for my SQL Server Express 2019. I'm using the script from https://github.com/olahallengren/sql-server-maintenance-solution

Here's the command I run from an elevated command prompt.

sqlcmd -E -S .\SQLEXPRESS -d master -Q 
"USE master; 
grant execute on dbo.master.DatabaseBackup to [TODDCOM\itadmin];
EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'c:\sql2019\backup', @BackupType = 'FULL', @Verify = 'Y', @CheckSum ='Y'" -b -o C:\SQL2019\Backup\DatabaseBackup.txt
robocopy c:\sql2019\backup \\NAS\backup\SQL\ /E /MIR /LOG:"\\NAS\Backup\robocopy-log.txt" /np

DatabaseBackup.txt reports this error:

Changed database context to 'master'. Msg 15151, Level 16, State 1, Server SPURR\SQLEXPRESS, Line 1 Cannot find the object 'DatabaseBackup', because it does not exist or you do not have permission. Msg 229, Level 14, State 5, Server SPURR\SQLEXPRESS, Procedure dbo.DatabaseBackup, Line 1 The EXECUTE permission was denied on the object 'DatabaseBackup', database 'master', schema 'dbo'.

I've used select suser_sname() to confirm that the account being used is the same as the one i gave permissions to. So far I've applied execute permissions to it in the following places:

  • the dbo schema in the master database.
  • the Security\Users folder has the 'domain\admin' account and it has the execute permissions applied on it as well.
  • right clicked on the master db (in SSMS) and applied execute privileges to the 'domain\admin' account in there.
  • right clicked on every user database in the system and added execute permissions to the 'domain\admin' account on all of them.

I'm assuming that I only need it in one place but I can't figure out which place to put the permissions. Can anyone help me figure out what I'm doing wrong?

UPDATE: I updated the commands and error report to show what we've tried from the comments.

UPDATE: Adding more info. I ran this code from the task scheduled batch file:

sqlcmd -E -S .\SQLEXPRESS -d master -Q "SELECT SUSER_NAME(); select name, USER_NAME(s.principal_id) AS Schema_Owner from sys.schemas s;" -o C:\SQL2019\Backup\User.txt

and it output:

domain\admin

(1 rows affected)
name                 Schema_Owner
-------------------- ----------------
dbo                  dbo
guest                guest
INFORMATION_SCHEMA   INFORMATION_SCHEMA
sys                  sys
db_owner             domain\admin
db_accessadmin       db_accessadmin
db_securityadmin     db_securityadmin
db_ddladmin          db_ddladmin
db_backupoperator    domain\admin
db_datareader        db_datareader
db_datawriter        db_datawriter
db_denydatareader    db_denydatareader
db_denydatawriter    db_denydatawriter

(13 rows affected)```
DauntlessRob
  • 765
  • 1
  • 7
  • 17
  • 4
    Regardless of what permissions you think you have, the error message is pretty clear - you don't. So either the permissions are missing or you're not running as who you think you are. For the latter, just do `select suser_sname()` via that same method. For the former, take a look at `sys.fn_my_permissions()`. Also, no need to run from an elevated prompt - your permissions in the OS don't matter, only those in SQL. – Ben Thul Mar 10 '23 at 01:24
  • Does it work unelevated? You can try to do: sqlcmd -E -S ... -q 'SELECT SUSER_NAME()" to see which user your stuff is running from, and then once again verify the rights in the master db. – siggemannen Mar 10 '23 at 08:33
  • Plus, maybe it's just me, but i wouldn't put any user stuff in master db... – siggemannen Mar 10 '23 at 08:34
  • @BenThul Thank you, I used that code to confirm that it is in fact using the user that I gave permissions to (the 'domain\admin' one). I'm still getting the same error though. – DauntlessRob Mar 10 '23 at 19:09
  • How did you grant permission to the stored proc? Were you able to confirm lack of permissions via `sys.fn_my_permissions()`? What does a query of `master.sys.database_permissions` show? – Ben Thul Mar 10 '23 at 20:46
  • @BenThul in SSMS i went to the procedure, right clicked on it, and added the 'domain/admin' account and gave it execute permissions. (among all the others). Although SSMS shows domain\admin having all these permissions everywhere I look (i must be missing something) the permissions command still doesn't show any execute priv's on anything. Just "connect" access. – DauntlessRob Mar 10 '23 at 23:12
  • I would suggest granting permissions via TSQL and see if that gives any error and/or clears up your issue. It's as easy as `grant execute on dbo.DatabaseBackup to [domain\admin];` – Ben Thul Mar 12 '23 at 00:37
  • @BenThul `Cannot find the object 'DatabaseBackup', because it does not exist or you do not have permission.` I tried that command, but I'm clearly struggling with how to give my account permissions. – DauntlessRob Mar 13 '23 at 20:54
  • As whom did you run the command? That is, does that account have the permissions to grant permissions to that proc? The error message suggests that it's either that or the proc doesn't exist (which, at this point, I'd also check!). – Ben Thul Mar 13 '23 at 22:26
  • @BenThul The account it's running from is the [domain/admin] account. It has to be because it's a batch file being run from windows task scheduler. Doesn't the last line of the error message prove that it does know the object? it gives its address directly. – DauntlessRob Mar 13 '23 at 23:01
  • @BenThul Also, I've run the grant command under my sa (full sql admin account) directly in SSMS. but the batch file still returns the same. (and received the 'command succeeded successfully' msg) – DauntlessRob Mar 13 '23 at 23:03
  • I meant "as whom did you run the grant statement"? As to your question of the error message proving the existence of the object - no. It knows that you tried to call something by that name (from your call to `EXECUTE dbo.DatabaseBackup`). Also, I just saw the edits you've made and I think there's a fundamental misunderstanding. My advice for the grant statement wasn't meant to be incorporated into the command you're running, but rather run by you, as an admin, as part of the necessary setup for this to run. – Ben Thul Mar 13 '23 at 23:12
  • If the former worked, it would imply that a permissions model isn't needed at all as anyone would be able to grant themselves any permission that they deemed necessary. – Ben Thul Mar 13 '23 at 23:12
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/252497/discussion-between-dauntlessrob-and-ben-thul). – DauntlessRob Mar 13 '23 at 23:15
  • grant execute on dbo.master.DatabaseBackup to [TODDCOM\itadmin]; Can this really work? Shouldn't it be master.dbo.DatabaseBackup. Probably the script isn't the actual script you're using – siggemannen Mar 13 '23 at 23:31

1 Answers1

2

After some more interactive investigation with OP, this appears to have been an orphaned user in the master database. This is why all the signs of "permissions have been granted to that user" checked out - because they had. But because the login wasn't getting mapped to the user appropriately (but was getting mapped to the guest user), the login wasn't getting those permissions. I had them run alter user [domain\admin] with login [domain\admin] and that fixed the issue.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68