Post reply

Backup Permissions and User

  • October 2, 2016 at 3:00 am

    #313595

    Hi all,

    Can I confirm on the following understanding

    q1) It doesn't matter which database a login/user is currently at as long as the user has the db role/permission (db_backupoperator) to backup the database, right ?

    e.g. userA has db_backupoperator role on dbA

    userB is currently at dbB now but he can issue backup database dbA command at dbB right ?

    q2) assuming i am login using windows authentication, is the backup being backup as the

    a) window login account

    or

    b) the logon account of SQL engine

    - when i backup to a folder, should i grant permission to the folder to the a) or b) ?

    Regards,

    Noob

    Regards,

    Noob

  • October 2, 2016 at 9:02 am

    #1904305

    Hello,

    The user should have db_backupoperator permission in the each database, which the backup will be performed by this user.

    To backup directory should have access SQL Server service account. Otherwise the message will be displayed "Cannot open backup device (access is denied.)"

    Regards

    AW

  • October 2, 2016 at 11:52 am

    #1904312

    To answer your questions:

    q1 - It doesn't matter. Note, the user isn't "at" a database. They have a database context for their connection. Either from login or from the USE statement. I just tested this. I created a user with accounts in two databases, a and b. I then gave this user db_backupoperator in database a. I connected to database b with this account, essenatially a "USE b" statement and then issued a backup command for database a. This worked fine.

    q2 - The service account.

    The user or login never executes a backup. We use that nomenclature, but it's incorrect. When you issue a BACKUP DATABASE command, you are requesting the SQL Server to run the backup. The service account actually runs this, and writes the file from the perspective of the SQL Server instance and its own permissions. So if you connect to a remote server and issue

    BACKUP DATABASE MASTER TO DISK = 'C:\master.bak'

    The backup is made on the server, on the c: drive there, not on your laptop/desktop. The ACL and permissions are based on the service account running the SQL Server database engine.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 3, 2016 at 10:27 am

    #1904470

    thank you guys! yeap i have also done the tests and observed the same behavior :w00t:

  • October 5, 2016 at 7:05 pm

    #1905073

    szejiekoh (10/3/2016)

    thank you guys! yeap i have also done the tests and observed the same behavior :w00t:

    I'm curious. You would have had to try this out anyway. Why didn't you do that before posting your question? Not trying to be snarky about this either. Was there something that prevented you from trying it?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 6, 2016 at 11:11 am

    #1905229

    Jeff Moden (10/5/2016)

    szejiekoh (10/3/2016)

    thank you guys! yeap i have also done the tests and observed the same behavior :w00t:

    I'm curious. You would have had to try this out anyway. Why didn't you do that before posting your question? Not trying to be snarky about this either. Was there something that prevented you from trying it?

    Hi Jeff,

    The honest answer would be.. I am not at the comfortable level enough to trust myself at this point. Rather then testing it out and doubt on the outcome, i would prefer to seek the correct affirmation here while testing it out myself as well.

    Hope I didn't appear as though i am here just to leech answers ;(

    Regards,

    Noob

Viewing 6 posts - 1 through 5 (of 5 total)

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