Doing Backup-DbaDatabase with user that does not have read or backup rights, log vs diff

[antura-stehak]

I have a script that loops over a list of databases and performs a log backup hourly and diff backup daily. For some reason the sql user the script operates as had lost the right to read and backup a specific database. This had the consequence that log backups performed after that happened where just skipped. and so where all subsequent log backups in the loop as well, the script just skipped to the finally part. Also, in the SQL server logs nothing is caught for this database (all the previous log backups are logged).

However, once the script was trigged to run with $backupType set to diff it gave a clear error back Microsoft.Data.SqlClient.SqlError: The server principal \"usernama\" is not able to access the database \"dbname\" under the current security context. Also, this backup operation is (of course) logged in the SQL server logs.

The actual snippet that performs the backup is

        try {
            Backup-DbaDatabase -SqlInstance $SqlServer -Database $DbName -FilePath $BackupPath -FileCount $FileCount -IgnoreFileChecks -CompressBackup -Checksum -Type $BackupType -EnableException
            Write-JsonLog INFO "$BackupType backup of $DbName completed successfully."
        }
        catch {
           "logging the error"
        }

I was hoping/expecting that I would get a similar error back when doing log backup instead of silently failing and skipping to the end.

Am I doing something wrong or misunderstanding something?

[potatoqualitee]

This is actually a bug, thank you for bringing it to our attention. I've created an issue and a PR and will have a fix in in the next release.

[antura-stehak]

Is there any way to determine from what version of dbatools this is fixed? I guess the PR in question is #10065 and it has been merged, but I do not fully grasp when that will be part of a released version of dbatools.

[antura-stehak]

After looking some more in the releases I come to the conclusion it is included with version 2.7.20.

[antura-stehak]

Final post: using 2.7.20 fixed my issue, now things behave as they should when the user does not have the right to read or backup.