This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice This article is not considered official documentation for K2 software and is provided “as is” with no warranties.

Objective

To impersonate a user when calling a SQL stored procedure using the "Set the impersonation user on the DB ContextInfo" service key option located in the SQL Service instance configuration. This will allow a user to be impersonated by the K2 service account when connecting to the SQL database, at the same time the CONTEXT_INFO would be passed to SQL containing the actual users name for use in stored procedures.

Before You Begin

While creating the SQL service instance ensure that the following is done:

  • Authentication Mode: Service Account
  • Set the impersonation user on the DB ContextInfo: true
  • Database - Required: Your database name.

How-to Steps

Implementation Steps:

1. Create the following stored procedure

CREATE PROCEDURE [dbo].[PassImpersonatedUser]
AS 
SELECT CAST(CONTEXT_INFO() AS nvarchar(100)) As "Impersonated User"; 
GO

2. Create a SQL service instance from the SmartObject Tester Tool pointing to the database on which the stored procedure has been deployed.

3. While creating the SQL service instance ensure that the following is done:

  • Authentication Mode: Service Account
  • Set the impersonation user on the DB ContextInfo: true
  • Database - Required: Your database name

Image

4. Create a SmartObject from the Stored Procedure.

5) Use the SmartObject in a List View.

When done, you should now be able to for example: use "bob" to execute the SmartObject derived from the Stored Procedure to impersonate the K2 Service account which will connect to SQL and still be able to know who the origional user was who initated the SmartObject.

Objective derived from this K2 document:
https://help.k2.com/onlinehelp/k2five/userguide/5.1/default.htm#ServiceBrokers/SQLServer/SQL-Server-Service.htm

Additional documentation includes: 
https://help.k2.com/onlinehelp/k2five/userguide/5.1/default.htm#ServiceBrokers/SQLServer/SQL-Server-Service.htm#DBContextInfo 
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-context-info-transact-sql?view=sql-server-2017