Red Gate forums :: View topic - Permissions needed to run EXECUTE master..sqlbackup
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup Previous Versions
SQL Backup Previous Versions forum

Permissions needed to run EXECUTE master..sqlbackup

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
jimholcomb



Joined: 07 Nov 2006
Posts: 2
Location: Raleigh, NC

PostPosted: Wed Mar 25, 2009 9:20 pm    Post subject: Permissions needed to run EXECUTE master..sqlbackup Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6586

PostPosted: Thu Mar 26, 2009 11:50 am    Post subject: Reply with quote

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:
Quote:
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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6586

PostPosted: Thu Mar 26, 2009 5:19 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Chris.Allen



Joined: 12 Mar 2009
Posts: 547

PostPosted: Mon Sep 14, 2009 8:27 am    Post subject: Reply with quote

I find this solves 80% of all such problems:

Run the Red Gate SQL Backup agent as Local System. Job done.
Back to top
View user's profile Send private message
mudluck



Joined: 22 Nov 2005
Posts: 1
Location: SLC

PostPosted: Mon Jul 12, 2010 11:24 pm    Post subject: I found the fix Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group