Solved

Minimal permission to backup a SQL 2008 DB using BE 12.5

Posted on 2010-11-12
2
1,733 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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
Viewers will learn how the fundamental information of how to create a table.

626 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