0

I'm trying to create SQL Management Studio user permissions for different users and different databases. I want to make sure that every user can only see one database and this user will also have to be able to backup and restore their databases. I have been playing around with the server roles and user mapping, but I cannot seem to find the right settings. With the sysdmin server role, a user is able to perform a restore, but then that user is also able to access other databases.

enter image description here

1

Privilege to backup the database part is straight forward.

You create 2 database:

CREATE DATABASE dbuser1
GO
CREATE DATABASE dbuser2
GO

Create 2 login:

CREATE LOGIN loginuser1 WITH PASSWORD ='Aa123456789!@#'
GO
CREATE LOGIN loginuser2 WITH PASSWORD ='Aa123456789!@#'
GO

Create 2 user from the login created above:

USE [dbuser1]
GO
CREATE USER [user1] FOR LOGIN [loginuser1]
GO
USE [dbuser2]
GO
CREATE USER [user2] FOR LOGIN [loginuser2]
GO

Make the users member of db_backupoperator in corresponding databases:

USE [dbuser1]
GO
EXEC sp_addrolemember db_backupoperator, [user1]
GO
USE [dbuser2]
GO
EXEC sp_addrolemember db_backupoperator, [user2]
GO

Now user1 can only take backup up of dbuser and user2 for dbuser2. If user2 attempt to backup dbuser1 following error message will be issued. Msg 916, Level 14, State 1, Line 1

The server principal "user2" is not able to access the database "user1" under the current security context. Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.

For restore you have to add the users to dbcreator fixed server role. Then user2 can restore dbuser1 with REPLACE option. That is something you do not want.

There is few solution discussed here and hope you can implement one of these for restricting restore.

0

You have to create the user at the instance level and then at the database level (there too is a security tab) you can assign individual users to backup role (at the db level) and to other roles such as db_datareader (in which case the user can read data) and/or db_datawriter (in which case the user can write/delete/update anything in that db.)

  • Hi Benjamin, Thank you for your answer. I have set up the roles accordingly, but still the user is not able to restore the database. Do you have any idea why? Because I would say with the dbcreator role I thought it should be possible, right? The message they get is this: Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and the path or file exists. The security priviliges are set up right and the file does exist. I can access them via the file explorer. – Bas Damen Jul 4 '17 at 9:53
  • Can the user see the folder on the machine from which you are restoring from? Is the user a member of the db_backupoperator role? – benjamin moskovits Jul 5 '17 at 14:44
0

If you have to set 1 user per database, you can do from Security -> Login

Here you can map the user with corrosponding database and set its rights.

image

  • Hi Prabhat G, I have seen the security screen but I can not seem to find the right settings where a user is not able to access all the databases (each user has three), but have full rights (including backup and restore) on their own databses. – Bas Damen Jul 4 '17 at 9:55
  • This might be vague and newbie but, can't we just tick the database in above screenshot and check mark all the 'database role membership' ? This way a user will have ALL the access over the selected database – Prabhat G Jul 4 '17 at 9:59
  • Thanks for your suggestion, but even then I can not make a backup of the specified database.I just left out denydatareader and denydatawriter and ticked all the other boxes. – Bas Damen Jul 4 '17 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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