Share via

Backup Database Permission Denied on master

AlphonseG 236 Reputation points
2024-01-02T02:19:53.38+00:00

Using SQL Server 2022 Developer Edition.

In SSMS logged in as sa, I can run

BACKUP DATABASE master TO DISK = N'C:\MSSQLSERVER\Backups\Master-Full DatabaseBackup.bak' WITH NOFORMAT, INIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

But if I create a stored procedure (in a different database) to do the same (still logged in as sa), I get the error permission denied.

create PROCEDURE dbo.uspBackupMasterTest WITH EXEC AS OWNER AS BEGIN SET NOCOUNT ON; BACKUP DATABASE master	TO DISK = N'C:\MSSQLSERVER\Backups\Master-Full DatabaseBackup.bak' WITH NOFORMAT, INIT, NAME = N'master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 END

Msg 262, Level 14, State 1, Procedure dbo.uspBackupMasterTest, Line 6 [Batch Start Line 26]

BACKUP DATABASE permission denied in database 'master'.

Msg 3013, Level 16, State 1, Procedure dbo.uspBackupMasterTest, Line 6 [Batch Start Line 26]

BACKUP DATABASE is terminating abnormally.

How can I resolve this?

Developer technologies | Transact-SQL
SQL Server | Other
Accepted answer
  1. Dan Guzman 9,401 Reputation points
    2024-01-02T19:00:31.5733333+00:00

    Is there a reason for EXEC AS OWNER in the proc? The execute as security context is sandboxed to the user database unless the database is TRUSTWORTHY.

    The proc should work without EXECUTE AS unless you have special requirements not evident in the code you provided.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.