Bruce,
Impersonation brings its own problems to the table. For one, you won't be able to track which "person" would be connecting or making changes unless you added in your own code in the application to pass the original user information along. This effectively
makes Auditing useless (inside of SQL Server) unless you roll your own. On top of that, account impersonation isn't always allowed at corporations due to the inherent security risks. What is to stop an end use from impersonating that account from outside of
the application? What happens if someone who knew the password to that account left and it had to be changed? What if your security policy is that impersonated accounts need to have their passwords changed every 30 days?
Not that this would be the best way to do it, but assuming you have a stored procedure API style interface in your database, execution context can be used such as execute as user. I'm personally not a big fan of cross database styles as the norm rather than
the exception. By that I mean if your model uses multiple databases constantly, taking the hit for cross database queries and etc, why not have them as different schemas in the same database? Obviously 3rd party products there isn't much you can do about,
but I wouldn't use constant cross database talk as the standard.
To point back to your first requirement, what does impersonating a single account really get you? It doesn't get group based management per se, and if an end user would figure out the username and password they would have direct access to the database. Access
to encrypted information would be done through the application I'm assuming, so you would have to create your own security inside of the application and outside of SQL Server, basically re-inventing the wheel.
What may work for you is if you use Active Directory groups, allow those groups to connect and assign the groups to a database role with only the access that they need. In order to access any of the sensitive information (say to update, etc) you could employ
either another AD group to say only managers that could select/update/delete/etc through another database role (which they would belong to say the users and managers groups and as such get the higher of the two priv sets) and the stored procedure to access
that information could even go one step further by checking assigned groups (though the users group wouldn't have access to this SP anyway so it wouldn't be possible for them to execute it per se). This would still allow auditing to happen natively, your first
requirement is met becuase you can still exercise AD groups, and your second isn't quite met as they would have access to it, but only if they belonged to the group that had permission to.
Just some items to think about.
Resource:
Account Impersonation
-Sean
