1

Background / Current Setup

I have a database in a SQL server that uses row level security to determine what records a user has access to.

Users will have two methods to interact with the database. Either they will connect directly or they will use a web application to interface with it.

Other than the admins the SQL servers allows users of specific Active Directory groups to connect or the windows user account running the application pool for the webserver.

In the database there are two tables that holds all users and which team (Defined by a common Active Directory group) they belong to. Row level security uses the information contained within these two tables to determine what records a user can view.

To make sure that the webserver returns the same records to a user interacting with it, I have made it so that the webserver user executes the following query REVERT; EXECUTE AS User = @requestingUser; before CRUDing data.

To be able to do this a user account needs to be created for the database with limited access and the webserver user needs to be able to impersonate this user.

Question:

Is it possible for me to set up the database or a process so that each record found in the users table is also a user allowed to access the database?

Note: Since I use Active directory groups to allow a user to connect to the SQL server I assume I only need to create / manage a user account that grants specific permissions on my database.

What I want to avoid:

For security reasons I don't want to grant permission to my webserver user to create user accounts on the database nor do I want them to be able to give themselves permission to impersonate a specific user.

The solution I see is to create another user for the SQL server with the ability to create users and the ability to grant another user (ie. webserver user) the ability to impersonate another user (ie. users part of an active directory group). Then I would run a program as them querying the DB every [some lapse of time: 1 minute or 30 minutes] to see if they need to create or delete user accounts. This will work but I don't want to create extra work for myself if there is a native way / feature within my SQL server that will do this for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.