Permissions needed to run EXECUTE master..sqlbackup

Forum for users of Red Gate SQL Backup tool

Moderator: Chris Auckland

Permissions needed to run EXECUTE master..sqlbackup

Postby jimholcomb » Wed Mar 25, 2009 9:20 pm

I'm trying to give a user permission to run the following SQL:

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [databasename] TO DISK = ''G:\\SQLBackup\\<AUTO>.sqb'' "'

and they get the following error message:

SQL error 15157: Setuser failed because of one of the following reasons: the database principal 'loginname' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

The login is a user in the database to be backed up but is only a member of the Public server role. They can backup the database using the native backups.

How can I give this user permission to backup the database?

Thanks,
Jim
jimholcomb
 
Posts: 2
Joined: Tue Nov 07, 2006 11:21 pm
Location: Raleigh, NC

Postby Brian Donahue » Thu Mar 26, 2009 11:50 am

Hi Jim,

That's a good question. The answer is that, since using the VDI interface to stream backup data requires more permissions than a standard SQL Server backup, it's absolutely necessary that the user doing the backup has sysadmin rights. This is a Microsoft requirement that we can do nothing to work around. According to the Microsoft Virtual Device Interface 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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Thu Mar 26, 2009 5:19 pm

Taking a second look at this, I think the sysadmin requirement is specific to the SQL Backup Agent Service startup account. The extended procedure will probably work as long as you give the user exec permissions on it. Setuser is being run by the SQL Backup Agent, so it's probably the service's account that has inadequate permissions.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Chris.Allen » Mon Sep 14, 2009 8:27 am

I find this solves 80% of all such problems:

Run the Red Gate SQL Backup agent as Local System. Job done.
Chris.Allen
 
Posts: 594
Joined: Thu Mar 12, 2009 4:17 pm

I found the fix

Postby mudluck » Mon Jul 12, 2010 11:24 pm

I installed red gate with one user that was a local admin on OS and sysadmin SQL Server. The account was then changed to one that was only an OS ADMIN and that is when the failure started added him in as a SQL sysadmin and started working there you go rights 101 masked as a set user error.

Enjoy
mudluck
 
Posts: 1
Joined: Tue Nov 22, 2005 4:32 pm
Location: SLC


Return to SQL Backup Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests