I'm trying to setup a data connection to a SQL Server for a user that would allow that user access to a single table in a database as opposed to all available tables in a database.
Does anyone know if a data connection string can be setup that would allow user access to a single table? If not, I'm open to other suggestions. Main goal is to give user access to a single table in a database that has multiple tables.
This would work for a single case. I'm looking for something that can be dynamic for multiple users. For example, I have just a couple of users for now, but one needs access to all tables in a database and one only to a single table in the same database. Later on, if more users will be onboarded, I'd like to find a way to accommodate different access types per user for the same database.
I can create two separate accounts and then create two separate data connections for each account. Then give one account limited access to the database and the other account a full access to the database. Then permission data connection that's uses account with full access to the user that needs a full access and permission data connection that uses account that has access to a single table to a user that needs access to a single table. However, as soon as a third user joins and will need another variation of an access, I'll need to create yet another data connection with another user account and modify database security for this third user.
Doable, but security team doesn't like creating and setting up dummy accounts that are used for data connections.
I'm looking for a way to give users access to contents in specific tables in a database without having to create dummy accounts and modifying database security for each account.