Speed up strategy for SQL Server when EXECUTE AS is used for security

I've just started looking at a system that implements security a little differently to the norm.

They create a new SQL user for each user of the system (of which there are about 32K now). Each query is sent via a connection that is initially using the SA account (lets not get bogged down on this), then after we know who the user is, the EXECUTE AS USER is used each query.

Now that there are so many users, creating new users and switching has a noticeable performance hit and the company is looking at improving the situation.

A few points: - SQL Code is dynamic sql (not stored procedures) - The original idea was to alleviate the need for the developers of the company to worry about writing SQL worrying about permissions - and let another layer worry about it.

How does one try and improve the query e开发者_运维百科xecution time and avoid the EXECUTE AS USER code and still get the same security scrutiny?

Does SQL Server support session variables to store a user account?

---

It's difficult to know whether this is helpful without a bit more detail on how your application security model and how it controls and/or pools database connections. Is it a fat client, n-tier or something else?

SQL connections can support a single "session variable" using CONTEXT_INFO - a user-configurable binary(128) field which persists for the duration of the connection. This may meet your requirements, but you need to beware that if you use it to store security information it will be accessible to the end user - you should therefore probably encrypt or salt and hash any security information in the CONTEXT_INFO to prevent users tampering with their permissions; this may have performance implications.

It may not be applicable depending on your application architecture, but have you considered switching to Windows authorisation and organising permissions though Active Directory users and groups?