Allow user to create databases on server and to see only the databases that are created by him, without creating login first

Question

I want to implement the following use case using C# application and MS SQL Server Express 2014. I have a centralized database server in which there are already created some database (which have the same tables). In my c# application I want to allow users to create databases (with same format but different names) and users can assign other users to have access to the newly created database. Also I want to assure that at the first use of the c# app the user shall not be able to see any database from the server address. Only after he executes the "Create db" button , he can view only the database which was previous created by him. Same thing shall happen for the other users when they will receive access on a certain database. Another requirement is that the database creator and the other users mapped on the recently created database to not be able to have access on the database server via SSMS.

So an example can be : suppose you have 3 databases (A,B,C) in sql server xyz . Now user AA opens the c# app and in the list of available database he see nothing so it creates a new one db1. After that db1 is present in the list. Then he assign users AB, AC to db1. Now AB, AC can see also db1. AA, AB, AC shall not have access to db1 database via SSMS.

What I've tried until now:

1. I have configured a laptop as a remote MS SQL Express 2014.
2. Using SSMS I first create a login, then I deny view to any database and after I allow access only on certain database

.

USE [master]
GO

CREATE LOGIN [xx] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

-- Step 2:(deny view to any database)

USE master;
GO
DENY VIEW ANY DATABASE TO [xx]; 

 -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::DB1 TO [xx];
GO

Now I don't know if it is possible to allow the creation of databases on server and to map users on database without creating a login first. Also I don't know how to restrict the access for SSMS usage.

Answer 1

Instead of making Logins for each user, you should use an Active Directory Group that each user is a part of (e.g. DOMAIN USERS), and assign permissions directly on that AD Group or on a Role you can create for that AD Group. Then you only need to DENY VIEW ANY DATABASE once.

Then when your user creates a database via your application is when you can run the CREATE LOGIN (if one doesn't exist) and ALTER AUTHORIZATION ON DATABASE scripts for that User and Database.

The benefit of doing it this way is all your Users will be unable to access any databases that aren't their own automatically, even via SSMS. You also can set the default database of the new Login to their first database they create too, and set appropriate permissions like giving them the db_owner role.

Answer 2

I was thinking about application roles but you have to create them in the database so it won't work since you need the right to create databases. (Application roles)

But, you could do a logon trigger to block connections via SSMS. Here's how to do it Limit SQL Server Login Authentication scope using a Logon Trigger That way, you create a login that have the right to create database, but it cannot connect via SSMS because of the logon trigger.

Here's other examples with the logon trigger.

To know which databases where created by which users, you could use a query like this one.

SELECT s.name as DatabaseName, sl.name as OwnerName 
FROM sys.databases s
JOIN sys.sql_logins sl ON sl.sid=s.owner_sid

Answer 3

A login can see a database (other than master and tempdb) only if

1. The login is the owner of the database
2. The session is currently connected to the database or
3. The login has VIEW ANY DATABASE

sys.databases

So the built-in permissions model doesn't accommodate your requirements. But since your users are accessing using a custom application, you can use a Signed Stored Procedure to allow the users to list databases any way you want, or perform any other administrative activity your application needs.