Here's the scenario: we use a 3rd party application that has a really awful idea of security. It uses Windows authentication, but requires direct database (and table) access, and generally doesn't work properly without full db_owner database access. We also
run a number of other applications that use Windows Authentication, and the collateral damage is that they have way more privileges than needed to run.
In an effort to improve this, if even only slightly, I'm trying to limit these excessive permissions to just this one application. Now, I know what you're thinking already - "Application roles!" I've looked into this, but as best I can tell, an application
role is a database principal, and not a server principal. This presents a problem, as this application needs to access multiple databases at once.
I've found that issuing EXECUTE AS LOGIN = 'dedicated_application_login' behaves exactly the way I would like, but only when issued in an ad-hoc batch. If I place it in a trigger or procedure, it seems to only affect execution of the module, and doesn't
permanently change the security context of the session. This prevents me from checking the application name in a login trigger and running EXECUTE AS if everything checks out - I can do it, but it won't really have any meaningful effect. I'm well aware this
is awful security as well, but i
View Complete Post