Sql-server – SQL Management Studio user permissions with multiple databases

sql serverssms

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

Best Answer

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.

Related Question