Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Minimal permission to backup a SQL 2008 DB using BE 12.5

Posted on 2010-11-12
2
Medium Priority
?
1,754 Views
Last Modified: 2012-05-10
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
Comment
Question by:basaral
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Accepted Solution

by:
Amgad_Consulting_Co earned 2000 total points
ID: 34125238
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
 
LVL 8

Expert Comment

by:PenguinN
ID: 34130110
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question