Sql Server Impersonation

As part of a new security model I’m working on, I’ve had to start looking at impersonation features within Sql Server. At the moment, I’m working with an ASP.NET 2.0 site which latches on to a Sql2005 backend. Traditionaly the path of least resistance for me has been to just wire in a Sql Server Authentication enabled connection string, and connect to the database through that login.

However, Ldap enabling the site to enable the user to log in to the system using their windows username/password; and then connecting to the database as that account via windows authentication & impersonation seems pretty appealing. From then on I should be able to implement both row & cell based security for each user/group that connects to the database.

The initial hurdle is how will the user be able to execute stored procedures that access functionality outside of their access? The answer is to use the Sql2005 “WITH EXECUTE AS ‘<Username>’” functionality whacked on to the end of the stored proc header.

So for instance I have a stored proc:

CREATE PROCEDURE SwitchContext WITH EXECUTE AS ‘SecureAccount’

as

begin

 

select current_user, suser_name()

 

execute as caller

select current_user, suser_name()

 

end

If I was to execute this procedure after logging into Sql Server using windows auth as user “domainadenhertog“, I should get the following output:

secureaccount | SecureAccount

dbo           | domainadenhertog

Brilliant – in the first select, the stored proc is running as the impersonated account I told it to using the “with execute as” snippet, after I punch in “execute as caller“, the context gets switched back to my connected account credentials and executes as me.