db_backupoperator can't back up database

  • I have a vanilla windows application login that needs the ability to back up a database as part of it's daily work. I've granted it db_owner and db_backupoperator on the database in question. But it just won't back up the database. Even using EXECUTE AS to test the login, I get a very generic error that tells me things aren't working.

    Msg 262, Level 14, State 1, Line 6

    BACKUP DATABASE permission denied in database 'MyDB'.

    Msg 3013, Level 16, State 1, Line 6

    BACKUP DATABASE is terminating abnormally.

    When the login is set to SysAdmin, the backup works perfectly. But it won't work with the backup operator role. I'm fairly certain that this means the problem is in SQL Server, not on the share level. But to be sure, I added the login to read / write on the share separate from the server group it's a part of.

    No dice. Backup works when login is sysadmin. Not when sysadmin is removed (public remains) and db_backupoperator is put on the database level. I even tried adding dbcreator on the server level, but no dice.

    Thoughts?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Sounds like maybe there's an explicit DENY in place.

    When a login is sysadmin the DENY won't affect anything because security checks are bypassed.

    Try running this in the database in question:

    SELECT pr.name,

    pm.permission_name,

    state_desc

    FROM sys.database_permissions pm

    INNER JOIN

    sys.database_principals pr ON pm.grantee_principal_id=pr.principal_id

    WHERE pm.state_desc='DENY'

    Cheers!

  • There were no deny perms initially. I double-checked, there are none now.

    Any other ideas?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Log

    user_name()

    suser_sname()

    etc. (there are a few more)

    into a table before the app runs the command (and the command). Check there isn't any impersonation.

    If you execute as user = 'login', can you run a backup?

  • Steve Jones - SSC Editor (4/26/2016)


    Log

    user_name()

    suser_sname()

    etc. (there are a few more)

    into a table before the app runs the command (and the command). Check there isn't any impersonation.

    We (the vendor DBA and I) checked that to verify the user account running the backup. It is exactly the account we were expecting. (Suser_sname() returns the actual account. User_Name() returns dbo. System_User returns the actual account.)

    The other commands (aside from the ones you specifically listed and System_User) are all listed in BOL as synonyms of the first two. Will adding them really make a difference?

    If you execute as user = 'login', can you run a backup?

    Nope. Tried that yesterday. Failed with the same error.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Found this doing a search:

    If you will assign user as db_backupoperator you will discover error when you will try to create a backup..

    When trying to specify location, you will see this:

    The EXECUTE permission was denied on the object 'xp_availablemedia', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

    Now, if you add this account to master database and grant execute permissions to 'xp_availablemedia', you will hit another issue -> selecting backup location..

    So If you will try to use provided location and add name for backup file you will see

    Cannot verify the existence of the backup file location. Do you want to use the backup file location anyway?

    if you click Ok, it may take a backup to this file. If you want to choice location you may see this:

    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

    So question is what else is required in order to let non sysadmin users ability to create database backup and do the restore..

    Same issues can be applied on restore procedure, if you select from a list it may let you restore if you need from device you may not be able to get this file

    Error will be

    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

  • Lynn Pettis (4/26/2016)


    Found this doing a search:

    If you will assign user as db_backupoperator you will discover error when you will try to create a backup..

    When trying to specify location, you will see this:

    The EXECUTE permission was denied on the object 'xp_availablemedia', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

    Now, if you add this account to master database and grant execute permissions to 'xp_availablemedia', you will hit another issue -> selecting backup location..

    So If you will try to use provided location and add name for backup file you will see

    Cannot verify the existence of the backup file location. Do you want to use the backup file location anyway?

    if you click Ok, it may take a backup to this file. If you want to choice location you may see this:

    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

    So question is what else is required in order to let non sysadmin users ability to create database backup and do the restore..

    Same issues can be applied on restore procedure, if you select from a list it may let you restore if you need from device you may not be able to get this file

    Error will be

    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

    Oh my gosh. Lynn, I might adore you!

    Which depends on whether or not this fixes my issue, but you've found something I haven't seen yet. THANK YOU.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Hrmpf.

    Nope. Doesn't fix it. Still with the permission error on BACKUP DATABASE. And the backup command works. I've printed it out to the job log and run it myself. So long as the account running the backup is sysadmin, there are no problems. It's when it's running as a non-sysadmin account that it suddenly doesn't have permissions.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Can you recreate the issue with a sandbox database in a test/development environment?

  • [headdesk]

    Seriously. SERIOUSLY?

    Because Public had a DENY on BACKUP DATABASE, it wouldn't let my account backup the database. I found it using the below code from this link.

    SELECT b.name, CASE a.protecttype WHEN 204 THEN 'GRANT_W_GRANT'

    WHEN 205 THEN 'GRANT' ELSE 'DENY' END AS rights

    FROM sysprotects a

    INNER JOIN sysusers b ON a.uid = b.uid

    WHERE a.action = 228

    ORDER BY b.name

    So I revoked BACKUP DATABASE permissions from Public (not granted). And now it's working.

    EDIT: I should note that before I did the revoke, I also did a GRANT BACKUP DATABASE to the account in question because I wanted to see if it helped or not (in addition to having the role). But the backup still failed. Then I found out about the Public DENY perms. I did not revoke the account grant before testing again, only the DENY on Public. So this is also a possible variable in the whole saga.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (4/26/2016)


    [headdesk]

    Seriously. SERIOUSLY?

    Because Public had a DENY on BACKUP DATABASE, it wouldn't let my account backup the database. I found it using the below code from this link.

    SELECT b.name, CASE a.protecttype WHEN 204 THEN 'GRANT_W_GRANT'

    WHEN 205 THEN 'GRANT' ELSE 'DENY' END AS rights

    FROM sysprotects a

    INNER JOIN sysusers b ON a.uid = b.uid

    WHERE a.action = 228

    ORDER BY b.name

    So I revoked BACKUP DATABASE permissions from Public (not granted). And now it's working.

    Yes, that is what DENY will do.

  • Lynn Pettis (4/26/2016)


    Can you recreate the issue with a sandbox database in a test/development environment?

    This is our test environment. We're testing the vendor move to SQL 2012 before moving it to QA and then production. But the problem is solved now. Thanks. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Lynn Pettis (4/26/2016)


    Brandie Tarvin (4/26/2016)


    [headdesk]

    Seriously. SERIOUSLY?

    Because Public had a DENY on BACKUP DATABASE, it wouldn't let my account backup the database. I found it using the below code from this link.

    SELECT b.name, CASE a.protecttype WHEN 204 THEN 'GRANT_W_GRANT'

    WHEN 205 THEN 'GRANT' ELSE 'DENY' END AS rights

    FROM sysprotects a

    INNER JOIN sysusers b ON a.uid = b.uid

    WHERE a.action = 228

    ORDER BY b.name

    So I revoked BACKUP DATABASE permissions from Public (not granted). And now it's working.

    Yes, that is what DENY will do.

    The thing is, we haven't touched the Public role for anything. This was a straight out of the box install. So I'm wondering if MS auto-denies everything to Public and I just missed that.

    EDIT: I forgot that we restored this db from our SQL 2k8 environment, which was a restore from the vendor's hosted environment. So it's possible they did the DENY on Public and forgot to mention it when helping me troubleshoot the problem.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • FYI, code using system views instead of the 2000 backward compatibility views:

    select

    dbprin.name PrincipalName,

    dbprin.type_desc,

    dbperm.class_desc,

    dbperm.permission_name,

    dbperm.state_desc

    from

    sys.database_principals dbprin

    inner join sys.database_permissions dbperm

    on dbprin.principal_id = dbperm.grantee_principal_id;

  • Thank you. 😀

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 15 posts - 1 through 15 (of 22 total)

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