I have a legacy application that uses ADO to connect to a local Access database for data storage. I converted the application to use SQL Server simply by changing the connection string and making minor adjustment to some queries. Works great. The problem now is security. I have about 100 users, and I would like each to have their own 'private' database on the SQL Server. My idea is each user will have a unique database login and that should isolate them from other users, preventing them from accessing anyone else's data, and preventing unauthorized used form accessing their data.
Now the application uses a database with about 40 tables, and each user will use the same tables, which in the past was fine because they had their own private copy of the database. So the corresponding SQL database is not going to be multi-tenant, I think each user needs their own database in SQL Server.
I was looking at schemas in SQL Server but I don't think this will help. I could create multiple databases for each user, migrate their data over, and setup a connection string for them.
The other issue is security. The SQL Server will have to be public so the application can access it.
So my question is, given my requirements, what is the 'best' way to accomplish this. I'm not looking for a complete end to end solution to my problem, it's just that I'm not familiar enough with the topic to know if I'm even on the right tract.