Backup Permissions and User

Question

Backup Permissions and User

szejiekoh

SSCarpal Tunnel

Points: 4155
More actions
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

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

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

adam9870 SSC Veteran Points: 210 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

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.

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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)

szejiekoh SSCarpal Tunnel Points: 4155 More actions · Answer 5

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