Solved

Minimal permission to backup a SQL 2008 DB using BE 12.5

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

756 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