{"id":96994,"date":"2023-05-26T19:42:33","date_gmt":"2023-05-26T19:42:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96994"},"modified":"2026-04-16T09:50:56","modified_gmt":"2026-04-16T09:50:56","slug":"unmasking-sql-server-dynamic-data-masking","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/unmasking-sql-server-dynamic-data-masking\/","title":{"rendered":"SQL Server Dynamic Data Masking: Introduction and Use Cases (Part 1)"},"content":{"rendered":"

SQL Server Dynamic Data Masking (DDM) is a security feature that hides column data from users who don’t have permission to see the unmasked values – without modifying the stored data. A masked column returns a redacted or randomised value (the type of masking is configurable per column) for users with standard SELECT permissions, while users granted UNMASK permission see the real values. <\/strong><\/p>\n

This article, Part 1 of a 3-part series, introduces DDM: how it differs from static data masking (which modifies the actual data) and from AlwaysEncrypted (which encrypts data at rest and in transit); DDM’s security model and known limitations; and the primary use cases where DDM is the right tool.<\/strong><\/p>\n

This is part of a series on Dynamic Data Masking by Ben Johnston<\/a>. For the rest of the series, click here<\/a><\/strong><\/p>\n\n

This is the beginning of a series on SQL Server Dynamic Data Masking. Dynamic Data Masking is a concept familiar with all developers and users of sensitive data. It is implemented in SQL Server with simplicity and elegance, requiring minimal changes to front end applications, including reporting, and almost no changes to queries. The series includes:<\/p>\n

    \n
  1. An in-depth introduction and use cases<\/li>\n
  2. Setup, examples, testing recommendations, coding alternatives to Dynamic Data Masking<\/li>\n
  3. Side-channel attacks<\/li>\n
  4. Attack mitigations and general architectural-considerations<\/li>\n<\/ol>\n

    The dynamic data masking feature was introduced in SQL Server 2016. Data masking addresses gaps in exposing data to end users when direct access is allowed via reporting tools, data analysis, machine learning, or any query tool. Masking is used to protect sensitive or proprietary data from users not authorized to view that data by obfuscating the actual data with a mask as it is returned to the user. Stated simply, actual data is not returned to the user but rather replaced with a traditional style mask hiding all or part of the configured column.<\/p>\n

    For instance, instead of seeing your coworker’s actual salary, something like 000.00 would be returned. Instead of seeing the proprietary name for a product, the mask of xxx would be returned. The mask can be all zeroes, in the case of a number, all x’s for characters, a pre-designated mask, or a completely custom mask.<\/p>\n

    When using front end applications with built in masking capabilities or custom code, this need has long been addressed. SQL Server dynamic masking instead addresses the masking need directly in the data engine. Implementing masking in the engine ensures data is protected regardless of the access method, reducing the work necessary to mask data in multiple user interfaces and reducing the chance of exposing unmasked data. The engine only presents data based on the security model, including masked or unmasked data.<\/p>\n

    In this introduction blog, I want to do two things. First, introduce masking as a general topic. Then as an appendix to this post, include the code to setup the rest of the series.<\/p>\n

    Competing Solutions<\/strong><\/h2>\n

    Other products exist that actually modify the data in the database, typically referred to as static data masking<\/strong>. This can be useful for moving production data to non-production environments. Since the data is actually modified, even users with escalated privileges in these environments can\u2019t override security and view the proprietary data. Data masked dynamically makes no actual changes to your data<\/p>\n

    The prime use case for physically masking the data is providing a cleansed copy of production data to non-production environments. This contrasts with dynamic data masking, with a use case of hiding sensitive data in production environments. They address very different use cases and some architectures may require both types of products.<\/p>\n

    Developers and other users with elevated privileges can easily bypass dynamic data masking in non-production environments. Since non-production environments aren\u2019t typically monitored as closely as production environments, physically masking the data in these environments is a good option.<\/p>\n

    Static masking can be especially important if government regulations are involved or any other situation where the chance of a data breach has to be reduced to the absolute minimum, which as a data professional, should be practically always. With static data masking, the original data is secure since it has been modified. But because the data is modified, some functionality is lost. Connections to external systems, some front-end application functionality, and some testing scenarios are difficult if not impossible.<\/p>\n

    Considering that the primary function of a database engine is maintaining data and ensuring the integrity of that data, there aren\u2019t many scenarios when static data masking is appropriate for production. Dynamic data masking requires careful planning and a dedicated plan to be sure it works. Modifying the data requires more planning and time.<\/p>\n

    Alternate Solutions<\/h2>\n

    Dynamic masking is presented as a possible solution when users are allowed to directly query data. There are caveats to this recommendation, including security concerns presented in later sections. Direct ad-hoc access to data isn\u2019t typically something you should allow your users to do against production transactional systems, so that wouldn\u2019t be a recommended use case. That leaves us with warehouses and reporting systems as the obvious candidates.<\/p>\n

    Using production data is often necessary to thoroughly test systems, especially system integrations. It can take an unfeasible amount of effort to setup data in all necessary interconnected systems and it is likely to still leave gaps in testing scenarios. It is possible to lock down the target data by means other than data masking. Separate schemas, alternate column names, encrypted columns, lookup tables with alternate keys, or simply excluding the target data are all possibilities depending on the business requirements. The primary strength of dynamic data masking is the low effort to implement. Testing and automatic upgrades done by Microsoft is another big strength. It is much harder to justify a custom solution when something is built into the product, patched, and enhanced by the vendor. Dynamic data masking can also be combined with restricting access via views and stored procedures to make it a more secure solution.<\/p>\n

    SQL Server\u2019s Always Encrypted<\/a> is another useful solution that meets a different need. The data is encrypted before it reaches the database engine. Client applications have the encryption key, usually in a key store, and send and receive encrypted data to the engine. It is up to the client application to mask data if that functionality is needed. One benefit of Always Encrypted is that data is encrypted before it hits the data engine. This means that even privileged users, such as administrators, can\u2019t see an unencrypted version of the data. Only authorized application users see the data.<\/p>\n

    Dynamic Data Masking Security<\/h2>\n

    When configured for a column, dynamic data masking is applied for all users unless they have specific authorization to view the unmasked data. Users need to be granted the UNMASK<\/code>, ALTER<\/code> ANY<\/code> MASK<\/code>, or CONTROL<\/code> permission on the database to view unmasked data. Starting with SQL Server 2022, the UNMASK<\/code> permission is more granular and allows unmasking down to a column level.<\/p>\n

    Supersets of these permission sets are included in the dbo<\/code> database role and the sysadmin<\/code> server role. If users only have SELECT<\/code> permissions and they don\u2019t have super user permissions, the data will be masked. When implementing a new security layer it is always important to verify that security is working as expected. In general, SQL Server security is additive, so if a user is accidentally put into a group or role with permission to unmask data, they will have those permissions.<\/p>\n

    Dynamic Data Masking Use Cases<\/h2>\n

    In this section I want to cover some of the primary use cases for the Dynamic Data Masking feature. While it isn\u2019t perfect (as I will cover in a later blog about how it can be defeated if you aren\u2019t careful), it has some very exciting uses.<\/p>\n

    Simply Hiding Sensitive Data from Users<\/h3>\n

    The primary purpose of dynamic data masking is to obfuscate proprietary data from unauthorized users. The data isn\u2019t changed, rather it is simply presented to users in a modified format or as a static value, such as 0 for integers. You can choose your own format if desired, so you might choose to include the first characters of a name or number, part of an email address including the @ sign so the data in the column is clear, or almost any user defined format.<\/p>\n

    It can also be used to hide data for authorized users in non-secure or public environments. Dynamic masking does this at the database engine level, thus making raw data access more secure. So, if data scientists, report users, testers, business analysts, product owners or other users are able to access data directly, it can still be protected. Dynamic queries, reports, data extracts are all protected. Users with unmasking privileges to the table or column see unmodified data.<\/p>\n

    Note that as noted, there are some security concerns with using Dynamic Data Masking in ad-hoc scenarios.<\/em><\/p>\n

    No Syntax Restrictions<\/h3>\n

    The dynamic portion of dynamic data masking is what makes it powerful. Columns configured with dynamic masking functions exactly like other columns, with the exception that the output differs depending on who accesses the data.<\/p>\n

    The masked columns can be used to JOIN<\/code> and for GROUP<\/code> clauses (the actual value is used, not the masked value you see). It can also be used for WHERE<\/code> clauses without using the masked values. It can be used in aggregations, functions like substring, in comparison operators, and in joins.<\/p>\n

    If a column with sensitive data is used as the primary key for a table, and that column is masked, it can still be used as the lookup column in your INNER JOIN<\/code> while keeping it secure. Histograms based on the salaries<\/code> of employees while keeping the salaries<\/code> confidential are possible. Data can be limited to a geographic region without allowing users to see the specific geographic data. All the data can be used for comparison operations, aggregations, joins, any operation normally used by data, while still keeping it reasonably confidential (Later in this series I will show examples of what I mean by “reasonably”.)<\/p>\n

    This is generally why the feature is mostly for building user interfaces, and not to secure data from nosy ad-hoc users,<\/p>\n

    Summary<\/h2>\n

    SQL Server Dynamic Data Masking was created to make it easier to obfuscate sensitive data. The relative ease of implementation makes it a nice additional layer when presenting SQL Server data. Future sections include detailed setup examples, caveats for use and security considerations.<\/p>\n

    ——————<\/p>\n

    <\/a>Appendix, Preparing the test database:<\/h2>\n

    The database I will be using in subsequent blogs is the sample database WideWorldImporters<\/code>, available in github<\/a>, using the latest version available. In this section I will provide instructions for setting up and initializing the database for the following blog entries.<\/p>\n

    Dynamic data masking was added to multiple tables and columns. The columns chosen were determined purely for demonstration purposes and were not evaluated for business utility.<\/p>\n

      \n
    1. Restore a copy of WideWorldImporters<\/code>\n
        \n
      1. For an on-Prem installation, use WideWorldImporters-STANDARD.bak<\/code><\/li>\n
      2. For an Azure database installation, use WideWorldImporters-STANDARD.bacpac<\/code>\n
          \n
        1. For verification in Azure, the lowest standard tier was used<\/li>\n
        2. The FULL<\/code> version can be used but requires a higher service tier due to the inclusion of memory optimized tables. If you try to import the FULL<\/code> version to a low tier model in Azure, you will get the following error:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n
          Warning SQL0<\/strong>: A project which specifies SQL Server 2016 as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.\n

          Error SQL72014<\/strong>: Framework Microsoft SqlClient Data Provider: Msg 40536, Level 16, State 2, Line 1 ‘MEMORY_OPTIMIZED tables’ is not supported in this service tier of the database.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

            \n
          1. Add users for testing scripts.\n
              \n
            1. MaskedReader<\/code> for viewing data in a masked state<\/li>\n
            2. UnmaskedReader<\/code> for viewing data in an unmasked state<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
              USE WideWorldImporters;\nGO\n \n\/*\n* Create datbase user without a login\n* Add to db_datareader for basic access\n* Add to [External Sales] for additional access via RLS\n*\/\nCREATE USER MaskedReader WITHOUT LOGIN;\nGO\n \nALTER ROLE db_datareader\nADD MEMBER MaskedReader;\nGO\n \nALTER ROLE [External Sales]\nADD MEMBER MaskedReader;\nGO\n \n \n\/*\n* Create datAbase user without a login\n* Add to db_owner for basic access\n* Add to [External Sales] for additional access via RLS\n*\/\nCREATE USER UnmaskedReader WITHOUT LOGIN;\nGO\n \nALTER ROLE db_owner\nADD MEMBER UnmaskedReader;\nGO\n \nALTER ROLE [External Sales]\nADD MEMBER UnmaskedReader;\nGO\n\n\/* \n* Disable Row Level Security\n* Some examples need this because customers will be \n* filtered out by location.\n*\/\nALTER SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole] \nWITH (STATE = OFF);\n\n<\/pre>\n
                \n
              1. Mask columns\n
                  \n
                1. As installed, WideWorldImporters<\/code> has 5 masked columns in the Purchasing.Suppliers<\/code> table and 5 in the corresponding temporal table, Purchasing.Suppliers_Archive<\/code>.<\/li>\n
                2. The following script adds masking to\n
                    \n
                  1. Application.Countries<\/code><\/li>\n
                  2. Additional columns to Purchasing.Suppliers<\/code><\/li>\n
                  3. Purchasing.SupplierTransactions<\/code><\/li>\n
                  4. Sales.Customers<\/code><\/li>\n
                  5. Sales.Orders<\/code><\/li>\n<\/ol>\n<\/li>\n
                  6. Note that corresponding columns in temporal tables are automatically masked when the parent table is masked.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
                    USE WideWorldImporters;\nGO\n \nALTER TABLE Application.Countries\nALTER COLUMN IsoAlpha3Code\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.Suppliers\nALTER COLUMN DeliveryLocation\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.Suppliers\nALTER COLUMN InternalComments\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.Suppliers\nALTER COLUMN PhoneNumber\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.Suppliers\nALTER COLUMN SupplierName\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN AmountExcludingTax\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN LastEditedWhen\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN OutstandingBalance\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN SupplierInvoiceNumber\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN TaxAmount\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN TransactionAmount\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Purchasing.SupplierTransactions\nALTER COLUMN TransactionDate\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Sales.Customers\nALTER COLUMN CreditLimit\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Sales.Customers\nALTER COLUMN CustomerName\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Sales.Customers\nALTER COLUMN DeliveryAddressLine1\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Sales.Customers\nALTER COLUMN DeliveryAddressLine2\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Sales.Customers\nALTER COLUMN DeliveryPostalCode\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n \nALTER TABLE Sales.Orders\nALTER COLUMN OrderDate\nADD MASKED WITH (FUNCTION = 'default()');<\/pre>\n
                    Setup Notes<\/h3>\n
                    https:\/\/github.com\/Microsoft\/sql-server-samples\/releases\/tag\/wide-world-importers-v1.0<\/a><\/p>\n
                    WideWorldImporters-STANDARD.bacpac<\/p>\n
                    WideWorldImporters-STANDARD.bak<\/p>\n
                    References<\/h3>\n
                    https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/dynamic-data-masking?view=sql-server-ver16<\/a><\/p>\n
                    http:\/\/wiki.gis.com\/wiki\/index.php\/Decimal_degrees<\/a><\/p>\n
                    https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/execute-as-transact-sql?view=sql-server-ver16<\/a><\/p>\n
                    https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/queries\/with-common-table-expression-transact-sql?view=sql-server-ver16<\/a><\/p>\n
                    https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/row-level-security?view=sql-server-ver16<\/a><\/p>\n
                    https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/encryption\/always-encrypted-tutorial-getting-started?view=sql-server-ver16&tabs=ssms<\/a><\/p>\n
                    https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/security\/encryption\/always-encrypted-database-engine?view=sql-server-ver16<\/a><\/p>\n\n\n
                    \n    
                    FAQs: SQL Server Dynamic Data Masking<\/h2>\n\n                        
                    1. What is SQL Server Dynamic Data Masking?<\/h3>\n            
                    \n                
                    Dynamic Data Masking (DDM) is a SQL Server feature that redacts column data for users who don’t have the UNMASK permission. When a masked column is queried by an unauthorized user, the returned value is replaced with a masked value defined by the masking function – for example, a credit card number might show as XXXX-XXXX-XXXX-1234, an email as aXXX@XXXX.com, or a random number within a defined range. The actual stored data is unchanged – only the presentation to unauthorized users is modified.<\/p>\n            <\/div>\n                    
                    2. How is Dynamic Data Masking different from SQL Server AlwaysEncrypted?<\/h3>\n            
                    \n                
                    AlwaysEncrypted encrypts the actual data stored in the database. The database engine itself cannot see the plaintext – data is only decrypted at the client application using an encryption key the client holds. DDM leaves data stored in plaintext and applies masking only at query time for unauthorized users. DDM is simpler to implement but provides weaker security – a DBA with UNMASK permission or a privileged query can see all data. AlwaysEncrypted provides stronger protection but requires application changes for encryption\/decryption.<\/p>\n            <\/div>\n                    
                    3. Who can see unmasked data in SQL Server Dynamic Data Masking?<\/h3>\n            
                    \n                
                    Users need either: (1) the UNMASK database permission granted explicitly; or (2) membership in the db_owner role or sysadmin server role. Regular users with SELECT permission on a masked column will see the masked value. UNMASK permission can be granted at the database, schema, table, or column level from SQL Server 2022, allowing fine-grained control over who sees what. One important limitation: a user with SELECT permission can still infer unmasked values through WHERE clause filtering – DDM does not prevent inference attacks.<\/p>\n            <\/div>\n                    
                    4. What are the DDM masking function types in SQL Server?<\/h3>\n            
                    \n                
                    SQL Server DDM supports four masking functions: (1) default() – hides the full value based on datatype (0 for numbers, XXXX for strings, 01-01-1900 for dates); (2) partial(prefix, padding, suffix) – shows specified prefix\/suffix characters; (3) email() – shows the first character plus XXX@XXXX plus the domain suffix; (4) random(low, high) – returns a random number in the specified range for numeric columns. Custom masking functions were introduced in SQL Server 2022.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
                    SQL Server Dynamic Data Masking (DDM) obfuscates column data for unauthorized users without changing the stored data. Part 1 introduces DDM: how it compares to static masking and AlwaysEncrypted, its security model, and primary use cases.…<\/p>\n","protected":false},"author":19670,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[2,143531],"tags":[159004],"coauthors":[98702],"class_list":["post-96994","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server","tag-benjohnston_dynamicdatamasking"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96994","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/19670"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96994"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96994\/revisions"}],"predecessor-version":[{"id":109950,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96994\/revisions\/109950"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96994"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96994"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96994"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96994"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}