| Author |
Message |
jimholcomb
Joined: 07 Nov 2006
Posts: 2
Location: Raleigh, NC
|
 Posted: Wed Mar 25, 2009 9:20 pm Post subject: Permissions needed to run EXECUTE master..sqlbackup |
 |
|
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 |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Thu Mar 26, 2009 11:50 am Post subject: |
|
|
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. |
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Brian Donahue
Joined: 23 Aug 2004
Posts: 6346
Location: Red Gate Software
|
| Posted: Thu Mar 26, 2009 5:19 pm Post subject: |
|
|
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
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
Chris.Allen
Joined: 12 Mar 2009
Posts: 443
|
| Posted: Mon Sep 14, 2009 8:27 am Post subject: |
|
|
I find this solves 80% of all such problems:
Run the Red Gate SQL Backup agent as Local System. Job done. |
|
| Back to top |
|
|
mudluck
Joined: 22 Nov 2005
Posts: 1
Location: SLC
|
| Posted: Mon Jul 12, 2010 11:24 pm Post subject: I found the fix |
|
|
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 |
|
|
|
|
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
