November 6, 2019 at 8:53 am
I'm working on an application that is Windows Forms accessing a SQL database. The application is sold to, and managed by other companies - we (the developers) don't have regular access to it once sold and implemented. They have their own SQL Servers on which their own database is installed.
Users of the application have to be registered (i.e. there's no anonymous access) and this is implemented via SQL security - all users are either SQL or Windows users in the database. This means certain users (ones with an appropriate role) need to be able to make other users. (And it might be that these other users will need to be able to make users, and so on.)
So far, we have achieved this by assigning such users the securityadmin role. This enables them to make other SQL users (which may themselves be assigned the securityadmin role). The problem with this approach is that it results in some users of our software having the securityadmin role, and the securityadmin role is quite a powerful one.
We are therefore wondering if there's a better way of supporting the functionality of users being able to make other (SQL) users. Fortunately, our application is entirely stored procedure based, so I've been able to try various approaches within that framework - "WITH EXECUTE AS ..." for example, but can't seem to get any to work. I'm wondering if it's "you can only impersonate database level permissions" as https://sqlstudies.com/2014/02/26/impersonating-a-server-level-permissions/ suggests.
One approach seems to involve making a separate, TRUSTWORTHY database and getting it to make users etc., but that seems surprisingly complicated, so I was wondering ...
How do other applications handle user management?
Could this be solved by making the database partially contained? Presumably, that would sidestep the "you can only impersonate database level permissions" limitation.
Our application also makes use of SSB, so this might be an alternative - put a message on a queue to make a new user and give the queue processor user the securityadmin role.
November 7, 2019 at 9:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 7, 2019 at 6:04 pm
You may want to try using signed stored procedures to create the users instead of using execute as. You don't hit the same limitations with sign stored procedures.
Sue
November 8, 2019 at 10:19 am
Thanks very much Sue. I wasn't aware of that feature of SQL. I'll investigate.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply