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:
- I have configured a laptop as a remote MS SQL Express 2014.
- 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.