[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1805
  • Last Modified:

Minimal permission to backup a SQL 2008 DB using BE 12.5

Currently BE is set up using a domain account having "sysadmin" rights to the 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 otions within the DB.  

I granted the login the "dbcreator" server role in case of a restore.  I also granted the account "db_backupoperator" and "db_owner" database roles for backup purposes.

When I run the BE backup, I get:

Backup- FRS V-79-65323-3265 -
An error occurred on a query to database Clarity6.
V-79-65323-3265 - The login has insufficient authority. Membership of the sysadmin role is required to use VIRTUAL_DEVICE with BACKUP or RESTORE.

Is my only option to leave this account with the "sysadmin" server role?  Or can I cut back it's power within the database? if so, what permissions do I need?
0
basaral
Asked:
basaral
1 Solution
 
Amgad_Consulting_CoCommented:
SQL Server requires that the user running that command be a member of the SQL Server sysadmin fixed server role. This is a requirement of SQL Server's Virtual Device Interface (VDI) subsystem, which SQL Backup leverages to gain access to the SQL Server backup data stream.

From the SQL Server VDI documentation:
"The system objects used to implement the virtual device set are secured with an access control list. This list permits access to all processes running under the account used by the primary client. Access is also permitted to processes running under the account used by Microsoft® SQL Server¿, as recorded in the system services configuration.
The server connection for SQL Server that is used to issue the BACKUP or RESTORE commands must be logged in with the sysadmin fixed server role. For more information, see Microsoft SQL Server Books Online.
The CreateEx (and Create) calls modify the security DACL on the process handle in the client process. Because of this any other modification of the process handle must be serialized with invocation of CreateEx."
0
 
PenguinNCommented:
Did you check the permissions on the Clarity6 database. If the users accounts "db_backupoperator" and "db_owner" have different rights or account settings I can imagine the backup will fail on this particular database.

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now