Solved

Minimal permission to backup a SQL 2008 DB using BE 12.5

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now