SQL Server Execute As Requires Individual Logins

Question

Evening,

I would like some practical confirmation in relation to an issue we are having.

We have a K2/SourceCode solution that turns upon the successful use of EXECUTE AS with Sql Server 2008 R2.

We have no direct control over how this solution is implemented, i.e. we cannot modify the queries that are submitted to the Sql Server engine. We can, of course, capture them using Profiler, and they tend to follow this pattern:

DECLARE @cookie VARBINARY(100); 
EXECUTE AS LOGIN = 'DOMAIN\username' WITH COOKIE INTO @cookie; 
SELECT @cookie;
exec [dbo].[SomeStoredProcedure] /* ... various params ...*/
exec sp_executesql N'REVERT WITH COOKIE = @cookie;',N'@cookie varbinary(100)',@cookie=/* some cookie value */

So what is happening is that [SomeStoredProcedure] is being executed in the security context of the user [Domain\username], with the service (application) account impersonating that user. Again, I emphasise that we have no control over this pattern. That's what the app does.

Outwardly this behaviour is perfectly-desirable, because we want things arranged in such a way that the stored procedure is effectively executed by whichever user is at the front-end of the application at the time.

However, these queries were consistently failing, and our investigation eventually led us to this, from the Sql Server documentation (my emphasis):

Specifying a User or Login Name The user or login name specified in EXECUTE AS must exist as a principal in sys.database_principals or sys.server_principals, respectively, or the EXECUTE AS statement fails. Additionally, IMPERSONATE permissions must be granted on the principal. Unless the caller is the database owner, or is a member of the sysadmin fixed server role, the principal must exist even when the user is accessing the database or instance of SQL Server through a Windows group membership. For example, assume the following conditions: CompanyDomain\SQLUsers group has access to the Sales database. CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has implicit access to the Sales database. Although CompanyDomain\SqlUser1 has access to the database through membership in the SQLUsers group, the statement EXECUTE AS USER = 'CompanyDomain\SqlUser1' fails because CompanyDomain\SqlUser1 does not exist as a principal in the database. If the user is orphaned (the associated login no longer exists), and the user was not created with WITHOUT LOGIN, EXECUTE AS will fail for the user.

We have a group of around 30 end users who need to be able to use this application, and the requirement is that the application security account must be able to impersonate any one of those users for the execution of these stored procedures. This requirement is fixed and non-negotiable.

The above documentation seems to preclude the possibility of meeting this requirement by adding all 30 users to an AD group, adding that group as a SQL Server login, and granting the group adequate permissions. And our practical testing results support this - EXECUTE AS fails.

Take one of those Users and give them their own, individual AD login on the Sql Server and the solution will work successfully for that user. EXECUTE AS succeeds, and the necessary permissions do not need to be assigned to the individual account because they have already been assigned by way of the AD group.

So, at this point, I am reasonably confident that I know what I am going to have to do. The requirement will be that every user has to have their AD account added as an individual Sql Server Windows login.

However, before I proceed with the rigmarole of implementing this, I wanted to ask the question publicly: is there something I am missing here?

It's instructive to imagine a similar scenario on an Enterprise-scale application - this model would somewhat fall apart, because of the need to add hundreds of individual, Windows-authenticated, Sql Server logins. Setting aside the possibility of automating this process, and the administrative burden that would ultimately result, I'm just finding it a bit of a stretch to imagine that this is the only way.

I would be grateful for confirmation and/or comments.

Thanks

Robert

Answer 1

As no-one has responded to the contrary (or indeed at all) we have assumed that BOL is accurate, and that there is no alternative resolution.