Table of Contents

Search

  1. Preface
  2. Introduction to Dynamic Data Masking
  3. Rules
  4. Connection Rules
  5. Security Rules
  6. Security Rule Set Simulator
  7. Masking Functions
  8. Stored Procedure Result Set Masking
  9. Integration with Informatica Products
  10. Appendix A: XML Functions Reference
  11. Appendix B: Glossary

Microsoft SQL Server Session Changing Commands

Microsoft SQL Server Session Changing Commands

The following table lists the session changing commands you can use with a Microsoft SQL Server database:
SQL Command
Description
Syntax
Masking is Affected
USE (Transact-SQL)
Changes the database context to the specified database or database snapshot in Microsoft SQL Server.
USE {database_name} [;]
Yes
SET ANSI_DEFAULTS ON
SET QUOTED_IDENTIFIER is modified depending on the value set to ANSI_DEFAULTS.
SET ANSI_DEFAULTS {ON | OFF}
Yes
SET QUOTED_IDENTIFIER (Transact-SQL)
Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.
SET QUOTED_IDENTIFIER { ON | OFF }
Yes
EXECUTE AS
When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name.
{ EXEC | EXECUTE } AS <context_specification> [;] <context_specification>::= { LOGIN | USER } = 'name' [ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ] | CALLER
Yes
The following table lists examples of the SQL commands in use:
Description
Example
SQL block that contains USE followed by a SELECT statement.
USE DDM SELECT * FROM EMPLOYEE
SQL block that contains SET ANSI_DEFAULTS followed by a SELECT statement.
SET ANSI_DEFAULTS OFF SELECT * FROM EMPLOYEE
SQL block that contains SET QUOTED_IDENTIFIER followed by a SELECT statement.
SET QUOTED_IDENTIFIER OFF SELECT * FROM EMPLOYEE
SQL block that contains only session changing commands.
USE DDM SET ANSI_DEFAULTS OFF SET QUOTED_IDENTIFIER OFF
SQL block that contains EXEC AS followed by SELECT statement.
EXECUTE AS USER = 'U21' SELECT * FROM EMP
EXECUTE AS LOGIN = 'loginA' SELECT * FROM EMP