1

One of my colleagues has Public role on SQL Management Studio but db_owner on a couple of DB. While trying to CREATE back-up, the following message pops up

Error while taking back-up

I have now added db_backupoperator on the DB, still the following error

enter image description here

2 Answers 2

2

The reason is that the user has enough permissions to perform a backup, but not enough permissions to enumerate the filesystem on the server, which is what SSMS does behind the scenes.

This behaviour is described in this support page, which suggests adding the login to the sysadmin role.

To work around it, add a backup device with sp_addumpdevice and let the user backup to that device.

6
  • the main idea was not to give the sysadmin and only let the user manage own databases..i have now added the user to sysadmin but now the user has all the privilages on all the databases
    – itsAftab
    Feb 23, 2016 at 10:43
  • let me put the question in another way then: can i create a role that includes sysadmin privilages ona few databases and assign it to a user?
    – itsAftab
    Feb 23, 2016 at 10:46
  • 1
    @itsAftab nope. Sysadmin is all or nothing. Have you tried with a backup device as suggested? Feb 23, 2016 at 11:02
  • i considered it but have opted not to use it...thank you for the advice though :)
    – itsAftab
    Feb 23, 2016 at 12:35
  • @itsAftab Could you explain why? Feb 23, 2016 at 12:56
0

This worked for me. It should be obvious but substitute your login and user names for what's below.

use master
grant view server state to [loginName]
alter server role dbcreator add member [loginName]
create user [userName] for login [loginName]
alter role db_owner add member [userName]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.