You can use the Set the impersonation user on the DB ContextInfo service key to configure a SQL Server service instance so that K2 passes current user information to Microsoft SQL Server.
The Authentication Mode of a SQL Server Service Instance might be set to use the K2 service account or Static SQL credentials, but you may need to pass the current user's identity to SQL. For example, you may want to write a log entry with the username of a user that executed a particular command in SQL, or perhaps you need to restrict access to data depending on the user who is executing the SmartObject. By setting the Set the impersonation user on the DB ContextInfo service key to true when configuring the SQL service instance, the specified Authentication Mode account is used to connect to SQL Server, but the current user's fully qualified name (FQN, for example, K2:Denallix\Bob) is used to set the SQL CONTEXT_INFO value. In your SQL server, you can then query the CONTEXT_INFO value to retrieve and use the user's FQN.
To enable passing of the user context to SQL, set the Set the impersonation user on the DB ContextInfo Service Key value to true.
To use the User Context Info passed in by K2, you can write a stored procedure or a user-defined function in SQL that retrieves, manipulates, and returns the user context value.
Here is an example of a function. Notice that it retrieves the CONTEXT_INFO value and performs some manipulation of this value.
To retrieve the context user value in a stored procedure that retrieves a record, for example, call the procedure or function that returns the context info:
To pass the user context info to SQL, you must create a SmartObject for the relevant items in the SQL Service service instance where you set the Set the impersonation user on the DB ContextInfo Service Key to true. When the SmartObject executes at runtime (for example, when a user executes the SmartObject method using a SmartForms view), K2 passes the user's K2 FQN to SQL.
In this example, there is a SQL table with loan request data and role information exposed as SmartObjects. These SmartObjects are configured in a SmartForms view so that users can create records in the Loan Requests table or read data from the table. Suppose that you need to use the Set the impersonation user on the DB ContextInfo feature to apply row-level security in SQL so that users can only read data from the Loan Requests table if they initially created the record or if they are a member of the Loan Admins role.
For more information about K2 and SQL Server see the following links: