Backup and Restore permission to user

Question

Post reply

Backup and Restore permission to user

SQL006

SSCertifiable

Points: 5071
More actions
April 9, 2012 at 6:03 am

#267143

Hi
I want to give a user for Backup and Restore permission, but without sysadmin role
is it possible
Pls help

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

a person with db_owner permissions has the ability to backup, restore, or even drop their own database, i believe.

that's very easy to test.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Otherwise the database role for backups is db_backupoperator.It grants BACKUP DATABASE, BACKUP LOG, CHECKPOINT in that database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL006 SSCertifiable Points: 5071 More actions · Answer 3

Lowell (4/9/2012)
a person with db_owner permissions has the ability to backup, restore, or even drop their own database, i believe.
that's very easy to test.

i had already given the db_owner permission but it showing error like this

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

the error shows i don't have permission on the server where sql server is hosted.

SQL006 SSCertifiable Points: 5071 More actions · Answer 4

GilaMonster (4/9/2012)
Otherwise the database role for backups is db_backupoperator.It grants BACKUP DATABASE, BACKUP LOG, CHECKPOINT in that database.

i tried this too but same error

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ensure that the SQL Server service account has permissions to the location the backup file will be written.

That's not a SQL permission error, it's a windows permission error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

dilipd006 (4/9/2012)
Lowell (4/9/2012)
a person with db_owner permissions has the ability to backup, restore, or even drop their own database, i believe.
that's very easy to test.
i had already given the db_owner permission but it showing error like this
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the file or path exists.
the error shows i don't have permission on the server where sql server is hosted.

Those are additional errors caused when the SQL Server service, or the user login, doesn't have haccess to a particular path. Those permissions are completely independent of SQL Server and are managed through Windows, Active Directory, or whatever other security configurations you have running.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQL006 SSCertifiable Points: 5071 More actions · Answer 7

Thanks for the reply

Can u explain how to give this permissions(windows) to sql serservice account.

The SQL server and sql server agent is run under Built in account(Local system)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Is the folder on the local machine or elsewhere? If elsewhere, you'll have to use a domain account for SQL Server, local system doesn't have any rights outside of the server.

btw, local system is not recommended for SQL Server service account. Too many permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ajay.g Ten Centuries Points: 1117 More actions · Answer 9

Hi,

If you manage Windows Level Security though Active Directory, you can provide him "Local Admin (LA)" permission on that Server to the person, if you wish to.

or else

you can only provide him permission on that particular folder (on Destination Server) with Read or Write or Full Access whatever he wants.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Piling on to what Gail said, you need to provide a domain account for SQL Server and give it permissions to that folder.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning