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

Hi..

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?
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.

Eugene ZCommented:
as per BOL
https://msdn.microsoft.com/en-us/library/ms186865.aspx
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:
https://msdn.microsoft.com/en-us/library/ms186858.aspx
<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).>
1

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
arnoldCommented:
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?
0
Mohammed QadeerAuthor Commented:
Hi..
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?

OR

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...
0
Introducing the "443 Security Simplified" Podcast

This new podcast puts you inside the minds of leading white-hat hackers and security researchers. Hosts Marc Laliberte and Corey Nachreiner turn complex security concepts into easily understood and actionable insights on the latest cyber security headlines and trends.

Eugene ZCommented:
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.

more:

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
http://www.symantec.com/connect/forums/best-permissions-backup-user-be-agent-sql-server

Understanding Logon Accounts and required User Rights Assignment to resolve connection, backup or restore failures
https://support.symantec.com/en_US/article.TECH130255.html


For more information about the SQL Agent, see the Backup Exec Administrator's Guide.
1
arnoldCommented:
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.
1
Aaron ShiloChief Database ArchitectCommented:
db_backupoperator - Members of the db_backupoperator fixed database role can back up the database.

https://msdn.microsoft.com/en-us/library/ms189121.aspx
1
Vitor MontalvãoMSSQL Senior EngineerCommented:
Mohammed, do you still need help with this question?
0
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?

http://www.experts-exchange.com/Software/Backup_Restore/Q_28458913.html

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

Thanks in advance..
SQL-Account-cannot-be-selected.png
Scenario.png
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.