Greetings,
I am on the latest SQL Backup And FTP and I really like this software! Everything work as expected.
Does any one know if it is possible to create a user in SQL server Management Studio that will have ability to create and delete new databases but prevent that user to do any changes to an existing production database.
In our case we have a “backup test user” that need to have the ability to create new databases, import data into the DB do some testing and finally delete the DB.
I would like to make sure he does not delete/overwrite the production DB when creating a restore job.
Any advice? Is that even possible ?
Regards
Hi Chris_Herrmann,
We’ll check the issue and get back to you once we have any news. Thank you!
Hi Alexander,
Thank you and also FYI:
1/ In this particular case we cannot have a separate test instance for testing purposes.
2/ I wanted to try to set an explicit deny for the backup test user on production database but it turns out that sqlbackupandftp cannot have one user for backup jobs and a different one for restore jobs ( My restore scenario: “restore to MS sql server (local)” ) which was an unexpected limitation.
Regards
Hi Chris_Herrmann,
To create a user who will have the permissions to remove a database, but will not have the permissions to edit it - within the framework of the DBMS, as far as we know, it is impossible.
With SQLBakcupAndFTP you can set different users for different jobs using the block comments during the connection, just specify the servers’ name like:
localhost /* any description of the connection */
localhost /* connection for the second user */
localhost /* any other connection */
and use different credentials or ports.
If you have any additional questions, please feel free to contact us.
For anyone trying to do something similar that is creation of a “restore user” that cannot overwrite a production db details.
This type of user does not exist in the world of MS so we need to improvise here.
I ended up with a user without any predefined roles and I added:
use [master]
GO
GRANT CREATE ANY DATABASE TO [restore-user]
GO
Create Any Database allows the login to have that power over databases they own, only.
This will also give the ability to restore.
By doing “Create Any Database” ONLY - you are giving the ability to create a new database, but avoiding the ability to Drop or Alter ANY database that you’d get by using the dbcreator fixed server role.
More details found here:
/regards
Hi Chris_Herrmann,
Thank you for your reply and for the details.
If you have any other questions, please let us know.
Thank you!
