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.
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
user1 can only take backup up of
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.
user2 can restore
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.
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.)