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;