What are the minimum permission required to backup the SQL Server 2012 DB using Symantec Back Exec 2014?


Currently BE is set up using a domain account having "sysadmin" rights to the SQL Server DB. Backup runs fine.  

I want to see if I can cut back on permissions to only allow this account to have backup and restore options within the DB.

Creating SQL User (SQL Server Authentication only) where-in this user should take Backups/Restores of DBs using BE 2014.
Is this possible?

I doesn't want Back-up Admin to have "sysadmin" privileges.

He should perform Backups/Restores only as a SQL User or using an account from/of Symantec Back Exec 2014.

Kindly, request you to provide detail Resolution.

Thanks in advance !
Mohammed QadeerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
as per BOL
for backup
<BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.>

for restore:
<If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).>

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I am puzzled by your inquiry.
why are you looking to limit the backup application rather than managing who can use/access the backup application/system?

who is authorized to use admin/backup exec?
Mohammed QadeerAuthor Commented:
Thanks for the quick response..

Scenario is, in my Company there are 12 different server like SQL Server - 1, SQL Server - 2, File Server, Exchange Server etc..

There is a System Administrator who ONLY takes care of Backups regularly using Symantec Back Exec 2014 for all the Co. Servers.

This System Administrator is NOT the SQL Server DBA, only Windows Administrator, but to take the Backups of SQL SERVERS, we provided him access with Windows Account (AD A/c) with full permission like "sysadmin" or "sa" of SQL Server.

I want to cut down this much of access from him.

Is there a way wherein we can allow this System Admin (only backup admin) to have limited access on SQL SERVER?

Is there any option that, we create only SQL User (using SQL Server Authentication) and he uses this A/c for Backups?


Is there any alternative where we create a user A/c in Symantec without sysadmin(SQL) privileges and allow user to take SQL Servers Backups?

Request you to provide Solution..
Thanks in advance...
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
A: you do not need to be sql sa role member to backup\restore databases (see above post)
If you use BE Sql agent: these permissions must be granted to the "service" (AD login) account

Thus , if your do not need this person to do manual direct restore \backup on sql server: you can delete his sql server login
otherwise: remove his sql sa role membership and grand just  db_backupoperator
Normally, as the main idea, all is done via BE sql agent... and backup exec console policies: where from all control and monitoring van be done
without going into sql server directly.


BESA backup is done by BE sql server agent (service): that you should set with "service logion"  as above permissions (not SA)
BE operator (human) should not have direct access to sql server (just on Box to install agent) :

Best permissions for backup user for BE agent - SQL server

Understanding Logon Accounts and required User Rights Assignment to resolve connection, backup or restore failures

For more information about the SQL Agent, see the Backup Exec Administrator's Guide.
IMHO, a person solely tasked with Backup operator roles as was pointed out should not have any credentials on the sql server. And usually will not be the person tasked with restoring a DB.
The restore requires not simply access to the restore agent but sufficient knowledge to check and make sure everything needed for the restore, i.e. if you are restoring a DB to a separate server, making sure logins needed for the DB are present.

There are other consideration i.e. how the DB is setup/log shipping/mirroring/..etc. that a restore without the information might become ...

Presumably, someone else DBA/Sysadmin have tested the backups to make sure they are functional on a regular interval.
Aaron ShiloChief Database ArchitectCommented:
db_backupoperator - Members of the db_backupoperator fixed database role can back up the database.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Mohammed, do you still need help with this question?
Mohammed QadeerAuthor Commented:
Thank you all... for providing useful information

my Testing with this problem is still going-on..:

Dear Mr. EugeneZ

now I'm facing this issues which is already raised, could you please explain this?


I need solution for this..
Please find the attachment(screenshots)

Thanks in advance..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.