User separation and security for applications

SQL Server Administration
1

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.

Thanks!

2

You are on the right track! Are you in a windows environment with active directory and all?

3

No, that will not be the case. Client machine can be anything anything from windows 7 to 10, most if not all not using active directory,

4

In that case, I would not recommend one database per user. not even the one schema per user approach, it would be a maintenance challenge.

What we have done the past is emulate what User Based authorization does (User-Based Authorization (C#) | Microsoft Learn)

Basically you would create a table with users another table of roles and a third table that maps users and roles. Based on that you configure your stored procedures or whatever fetches the data to pass in the unique user identifier (if you had AD or LDAP enabled that would map to samaccountname) and you use that to map to the underlying table to filter the data that specific user has permissions for. One DB approach. I hope I did not go way into end to end solution, but I am afraid I did. Please ask away and others might have better options that worked for them.

5

As a bit of a sidebar, 2008R2 has been out of support for quite a bit. Now would be a good time to upgrade so that you can more easily take advantage of row level security (RLS) or other tools.

1 Like