Post reply

NT AUTHORITY\SYSTEM Schedule Backups - SQL 2016 EXPRESS

  • April 8, 2021 at 3:24 pm

    #3866891

    Hi 🙂

    I'm trying configure best backups pratices in Windows server 2019 using sqlcmd in Windows Schedule Tasks with NT AUTHORITY\SYSTEM account, but it's not working... It only works if a setup this account with sysadmin role, if I setup only with public role I get this error:

    Msg 229, Level 14, State 5, Server EC2AMAZ-FFO8O5O, Procedure DatabaseBackup, Line 1

    The EXECUTE permission was denied on the object 'DatabaseBackup', database 'master', schema 'dbo'.

    I read that to make backups SQL only need to setup public role, is that right? Could someone help me with this?

    Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64>

  • April 8, 2021 at 8:06 pm

    #3866985

    Hi, you need db_backupoperator role on db level, so you need to create a user for this login in each db and grant it.

    ALTER ROLE db_backupoperator ADD MEMBER [NT AUTHORITY\SYSTEM];

    But the error you get is just saying that you need to grant execute on this backup procedure as you are not issuing backup statement directly but using proc, if you didn't change location for Ola's proc then:

    use master
    go
    GRANT EXECUTE ON OBJECT::dbo.DatabaseBackup to [NT AUTHORITY\SYSTEM];

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply