Permissions needed to run EXECUTE master..sqlbackup

jimholcomb

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

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE i]databasename[/i 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

Brian Donahue

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

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.

Chris.Allen

I find this solves 80% of all such problems:

Run the Red Gate SQL Backup agent as Local System. Job done.

mudluck

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