SQL Server User Impersonation Configuration

To find the appropriate database user credentials for CICS transactions or batch jobs, the SQL Server XA switch module issues the Transact-SQL EXECUTE AS statement. For the EXECUTE AS statement to work properly, you must include a GRANT IMPERSONATE statement in your SQL Server login/user ID security environment. This enables the xa_open user ID to impersonate the appropriate database user ID.

The following is provided as a simple example. Your security and database environment might dictate a different implementation. Please review the Microsoft documentation on the EXECUTE AS statement and other related topics before implementing your solution:

1. Create the user cited in the xa_open string:

   CREATE LOGIN [XAOPENUSER] WITH PASSWORD=N'XAOPEN', 
                 DEFAULT_DATABASE=[mfdb], DEFAULT_LANGUAGE=[us_english], 
                 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
   CREATE SCHEMA [XAOPENUSER]
   CREATE USER [XAOPENUSER] FOR LOGIN [XAOPENUSER] WITH 
                 DEFAULT_SCHEMA=[XAOPENUSER]

2. Create the user FRITZ, without login capability, to execute CICS or JCL:

   CREATE SCHEMA [FRITZ]
   CREATE USER [FRITZ] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[FRITZ]

3. Create user ALLIE, with login capability, to execute CICS or JCL:

   CREATE SCHEMA [ALLIE]
   CREATE USER [ALLIE] FOR LOGIN [ALLIE] WITH DEFAULT_SCHEMA=[ALLIE]

4. Grant database privileges to FRITZ and ALLIE:
   Note: This is example code. Your own code might require modification to comply with your specific database security policies.

   ALTER ROLE [db_owner] ADD MEMBER [SCOTT]
   ALTER ROLE [db_owner] ADD MEMBER [ALLIE]
   GRANT SELECT ON SCHEMA1.TABLE1 TO SCOTT
   GRANT ALL  ON SCHEMA1.TABLE1 TO ALLIE

5. Grant impersonate privileges to the xa_open string user to enable the xa_open string user to become ALLIE or FRITZ:
   Note: Once again, this is example code.

   GRANT IMPERSONATE ON USER:: FRITZ TO XAOPENUSER;
   	GRANT IMPERSONATE ON USER:: ALLIE TO XAOPENUSER;