{"id":772,"date":"2024-10-01T08:00:00","date_gmt":"2024-10-01T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=772"},"modified":"2024-09-27T17:40:29","modified_gmt":"2024-09-27T22:40:29","slug":"the-hidden-layers-of-dynamic-data-masking-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=772","title":{"rendered":"The Hidden Layers of Dynamic Data Masking in SQL Server"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>Dynamic Data Masking (DDM) is a powerful feature in SQL Server designed to prevent unauthorized access to sensitive data. By applying masking rules to specific columns in your database tables, DDM limits data exposure by obfuscating the data for users who shouldn&#8217;t see it, without altering the actual data stored in the database.<\/p>\n<h2>Types of Masks<\/h2>\n<p>SQL Server provides four types of masking functions to suit different data protection needs:<\/p>\n<ul>\n<li><strong>Default Mask<\/strong>: Applies full masking based on the data type of the field. For example, a string field would display as &#8220;<code>XXXX<\/code>&#8221; when masked.<\/li>\n<li><strong>Email Mask<\/strong>: Reveals only the first letter of an email address and replaces the rest with &#8220;<code>XXX<\/code>&#8220;, ending with &#8220;<code>.com<\/code>&#8220;. For instance, &#8220;<code>aXXX@XXXX.com<\/code>&#8220;.<\/li>\n<li><strong>Custom String Mask<\/strong>: Exposes the first and last characters of a string and replaces the middle characters with a custom padding string. For example, &#8220;<code>KXXXa<\/code>&#8220;.<\/li>\n<li><strong>Random Mask<\/strong>: Specifically for numeric data types, this mask replaces the original value with a random number within a specified range.<\/li>\n<\/ul>\n<p>It&#8217;s important to note that only the displayed data is masked; the underlying data in the database remains unchanged. This allows authorized users or roles\u2014such as application administrators\u2014to access the real data when necessary.<\/p>\n<h2>Permissions and Access Control<\/h2>\n<p>By default, the database owner (<code>dbo<\/code>) has permission to view unmasked data, adhering to the principle that the database owner should have unrestricted access. However, you can modify this behavior by granting or revoking permissions using appropriate roles and commands:<\/p>\n<pre><code>GRANT UNMASK TO [username];<\/code><\/pre>\n<p>This flexibility allows you to define a set of privileged users or roles who can view unmasked data, enhancing your data security management.<\/p>\n<h2>Can Users Modify Masked Data?<\/h2>\n<p>A common question is whether DDM prevents users from performing updates or other Data Manipulation Language (DML) operations on masked columns. The short answer is <strong>no<\/strong>. Users without the <code>UNMASK<\/code> permission cannot see the masked data, but they can still perform update operations on it. This might seem counterintuitive, but it allows data to be modified without exposing its contents to unauthorized users.<\/p>\n<h2>UNMASK Permission and Performance Walkthrough<\/h2>\n<p>Let&#8217;s explore this behavior with a practical example. You can run the following script in SQL Server Management Studio (SSMS) to follow along.<\/p>\n<pre><code>-- Demonstrates the UNMASK permission behavior with other DML permissions\n-- Also explores the performance overhead\n\n-- Create a sample database\nUSE master;\nGO\n\nIF (DB_ID(N'MaskingDemo') IS NOT NULL)\nBEGIN\n    ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\n    DROP DATABASE [MaskingDemo];\nEND\n\nCREATE DATABASE MaskingDemo;\nGO\n\n-- Create a table with a masked column\nUSE MaskingDemo;\nGO\n\nCREATE TABLE dbo.T1\n(\n    Col1 VARCHAR(10) MASKED WITH (FUNCTION = 'default()')\n);\nGO\n\n-- Insert some data\nINSERT INTO dbo.T1 (Col1) VALUES ('abc');\nGO\n\n-- View the data as a sysadmin\nSELECT * FROM dbo.T1;\nGO\n\n-- Create an unprivileged user\nCREATE USER User1 WITHOUT LOGIN;\nGO\n\nGRANT SELECT TO User1;\nGO\n\n-- Impersonate the unprivileged user\nEXECUTE AS USER = 'User1';\nGO\n\nSELECT SYSTEM_USER;\nGO\n\n-- View the data as the unprivileged user\nSELECT * FROM dbo.T1;\nGO\n\n-- Attempt to query the exact row\nSELECT * FROM dbo.T1 WHERE Col1 = 'abc';\nGO\n\n-- Additional queries to test data visibility\nSELECT * FROM dbo.T1 WHERE Col1 &gt; 'aaa';\nGO\n\nSELECT * FROM dbo.T1 WHERE Col1 &lt; 'aaa';\nGO\n\nSELECT * FROM dbo.T1 WHERE Col1 BETWEEN 'aaa' AND 'zzz';\nGO\n\nSELECT * FROM dbo.T1 WHERE Col1 BETWEEN 'BBB' AND 'zzz';\nGO\n\n-- Attempt to update the record\nUPDATE dbo.T1 SET Col1 = 'xyz';\n-- Expected error due to insufficient permissions:\n-- Msg 229, Level 14, State 5, Line 63\n-- The UPDATE permission was denied on the object 'T1', database 'MaskingDemo', schema 'dbo'.\nGO\n\n-- Revert to sysadmin\nREVERT;\nSELECT SYSTEM_USER;\nGO\n\n-- Conclusion: Dynamic Data Masking only obfuscates the data and does not prevent users from querying it.<\/code><\/pre>\n<p>As demonstrated, even after granting <code>SELECT<\/code> permission to the user, the data remains masked in query results unless the user has the <code>UNMASK<\/code> permission.<\/p>\n<h3>Granting Update Permission<\/h3>\n<pre><code>-- Grant UPDATE permission to the user\nUSE MaskingDemo;\nGO\n\nGRANT UPDATE TO User1;\nGO\n\n-- Impersonate the user again\nEXECUTE AS USER = 'User1';\nGO\n\nSELECT SYSTEM_USER;\nGO\n\n-- Update the record\nUPDATE dbo.T1 SET Col1 = 'xyz';\nGO\n\n-- Verify that the data is still masked\nSELECT * FROM dbo.T1;\nGO\n\n-- Attempt to delete the record\nDELETE FROM dbo.T1 WHERE Col1 = 'xyz';\n-- Expected error if DELETE permission is not granted:\n-- Msg 229, Level 14, State 5, Line 101\n-- The DELETE permission was denied on the object 'T1', database 'MaskingDemo', schema 'dbo'.\nGO\n\n-- Revert to sysadmin\nREVERT;\nSELECT SYSTEM_USER;\nGO\n\n-- Conclusion: Granting DML permissions does not override the masking; data remains masked in query results.\n<\/code><\/pre>\n<h2>Performance Considerations<\/h2>\n<p>Now, let&#8217;s examine the performance overhead introduced by DDM.<\/p>\n<pre><code>-- Performance difference exploration\n\n-- Create a test table without masking\nUSE MaskingDemo;\nGO\n\nDROP TABLE IF EXISTS dbo.T2;\nGO\n\nCREATE TABLE dbo.T2\n(\n    RowId INT IDENTITY(1,1) NOT NULL,\n    Col1 VARCHAR(10) NOT NULL,\n    CONSTRAINT CX_T2_RowId PRIMARY KEY CLUSTERED (RowId),\n    INDEX IX_Col1 NONCLUSTERED (Col1)\n);\n\n-- Create an identical table with a masked column\nDROP TABLE IF EXISTS dbo.T3;\nGO\n\nCREATE TABLE dbo.T3\n(\n    RowId INT IDENTITY(1,1) NOT NULL,\n    Col1 VARCHAR(10) MASKED WITH (FUNCTION = 'default()') NOT NULL,\n    CONSTRAINT CX_T3_RowId PRIMARY KEY CLUSTERED (RowId),\n    INDEX IX_Col1 NONCLUSTERED (Col1)\n);\n\n-- Insert data into T2\nSET NOCOUNT ON;\nDECLARE @BatchSize INT = 10000;\nDECLARE @i INT = 1;\nWHILE @i &lt;= @BatchSize\nBEGIN\n    DECLARE @val VARCHAR(10) = CAST(@i AS VARCHAR(10));\n    INSERT INTO dbo.T2 (Col1) VALUES (@val);\n    SET @i += 1;\nEND;\n\n-- Insert additional batches for faster loading\nDECLARE @NumberOfBatches INT = 99;\nDECLARE @CurrentBatch INT = 1;\nWHILE @CurrentBatch &lt;= @NumberOfBatches\nBEGIN\n    DECLARE @BatchStartValue INT = @BatchSize * @CurrentBatch;\n    INSERT INTO dbo.T2 (Col1)\n    SELECT CAST(RowId + @BatchStartValue AS VARCHAR(10)) FROM dbo.T2 WHERE RowId &lt;= @BatchSize;\n    SET @CurrentBatch += 1;\nEND;\nGO\n\n-- Rebuild indexes to eliminate fragmentation\nALTER INDEX CX_T2_RowId ON dbo.T2 REBUILD;\nALTER INDEX IX_Col1 ON dbo.T2 REBUILD;\nGO\n\n-- Copy data to T3\nINSERT INTO dbo.T3\nSELECT Col1 FROM dbo.T2 ORDER BY RowId;\n\n-- Rebuild indexes on T3\nALTER INDEX CX_T3_RowId ON dbo.T3 REBUILD;\nALTER INDEX IX_Col1 ON dbo.T3 REBUILD;\n\n-- We now have two identical tables except for the masking on T3\n\n-- Enable 'Include Actual Execution Plan' in SSMS (Ctrl+M)\n\n-- Execute both queries simultaneously\nSELECT * FROM dbo.T2; -- Query 1\nSELECT * FROM dbo.T3; -- Query 2\n\n-- Analyze the execution plans and note differences in cost and performance<\/code><\/pre>\n<p><strong>Analysis:<\/strong><\/p>\n<ul>\n<li><strong>Execution Plan Costs:<\/strong>\n<ul>\n<li>Query 1 (unmasked): ~49%<\/li>\n<li>Query 2 (masked): ~51%<\/li>\n<\/ul>\n<\/li>\n<li><strong>Estimated Subtree Costs:<\/strong>\n<ul>\n<li>Query 1: 2.926<\/li>\n<li>Query 2: 3.029<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>The slight overhead in Query 2 is due to the <code>Compute Scalar<\/code> operator added by the masking function. However, the additional cost is minimal, even when scanning a million rows. The overhead is proportional to the number of rows that need masking, and queries that use index seeks will experience less overhead compared to full table scans.<\/p>\n<h2>Permission Changes in SQL Server 2022<\/h2>\n<p>Starting with SQL Server 2022, you can exercise more granular control over who can view unmasked data. You can grant or revoke the <code>UNMASK<\/code> permission at the database, schema, table, or column level to users, database roles, Microsoft Entra identities, or groups. This enhancement allows for more precise data security management.<\/p>\n<p>For example, to grant <code>UNMASK<\/code> permission on a specific column:<\/p>\n<pre><code>GRANT UNMASK ON Schema.Table(Column) TO Role;<\/code><\/pre>\n<h2>Conclusion<\/h2>\n<p>Dynamic Data Masking acts as a protective layer over your sensitive data, ensuring that unauthorized users see only obfuscated versions of the data while the underlying information remains unchanged in the database. It&#8217;s a powerful tool for enhancing data security without requiring significant changes to your database structure or application logic. By understanding and implementing DDM effectively, you can safeguard your data against unauthorized access while maintaining necessary functionality for authorized users.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[35,120,12,29,40],"tags":[70,293,292,290,121,295,131,294],"class_list":["post-772","post","type-post","status-publish","format-standard","hentry","category-data-integrity","category-encryption","category-internals","category-security","category-sql-server-2022","tag-data-masking","tag-data-obfuscation","tag-data-security","tag-ddm","tag-dynamic-data-masking","tag-masking-functions","tag-sql-server","tag-unmask-permission"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>The Hidden Layers of Dynamic Data Masking in SQL Server - SQL Table Talk<\/title>\n<meta name=\"description\" content=\"Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqltabletalk.com\/?p=772\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Hidden Layers of Dynamic Data Masking in SQL Server - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=772\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-01T13:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Yvonne Vanslageren\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Yvonne Vanslageren\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=772#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=772\"},\"author\":{\"name\":\"Yvonne Vanslageren\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082\"},\"headline\":\"The Hidden Layers of Dynamic Data Masking in SQL Server\",\"datePublished\":\"2024-10-01T13:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=772\"},\"wordCount\":605,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"data masking\",\"Data Obfuscation\",\"Data Security\",\"DDM\",\"dynamic data masking\",\"Masking Functions\",\"SQL Server\",\"UNMASK Permission\"],\"articleSection\":[\"Data Integrity\",\"Encryption\",\"Internals\",\"Security\",\"SQL Server 2022\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=772#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=772\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=772\",\"name\":\"The Hidden Layers of Dynamic Data Masking in SQL Server - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2024-10-01T13:00:00+00:00\",\"description\":\"Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=772#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=772\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=772#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Hidden Layers of Dynamic Data Masking in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082\",\"name\":\"Yvonne Vanslageren\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g\",\"caption\":\"Yvonne Vanslageren\"},\"url\":\"https:\/\/www.sqltabletalk.com\/?author=2\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Hidden Layers of Dynamic Data Masking in SQL Server - SQL Table Talk","description":"Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqltabletalk.com\/?p=772","og_locale":"en_US","og_type":"article","og_title":"The Hidden Layers of Dynamic Data Masking in SQL Server - SQL Table Talk","og_description":"Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.","og_url":"https:\/\/www.sqltabletalk.com\/?p=772","og_site_name":"SQL Table Talk","article_published_time":"2024-10-01T13:00:00+00:00","author":"Yvonne Vanslageren","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Yvonne Vanslageren","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=772#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=772"},"author":{"name":"Yvonne Vanslageren","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082"},"headline":"The Hidden Layers of Dynamic Data Masking in SQL Server","datePublished":"2024-10-01T13:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=772"},"wordCount":605,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["data masking","Data Obfuscation","Data Security","DDM","dynamic data masking","Masking Functions","SQL Server","UNMASK Permission"],"articleSection":["Data Integrity","Encryption","Internals","Security","SQL Server 2022"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=772#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=772","url":"https:\/\/www.sqltabletalk.com\/?p=772","name":"The Hidden Layers of Dynamic Data Masking in SQL Server - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2024-10-01T13:00:00+00:00","description":"Dynamic Data Masking (DDM) in SQL Server is a feature that helps prevent unauthorized access to sensitive data by obfuscating it at the display level. This blog post discusses the various types of masks available, such as default, email, custom string, and random masks. We explore permissions and access control, demonstrating how users can modify masked data without viewing its actual content. Additionally, we discuss the new permission enhancements in SQL Server 2022 that allow for more granular control over unmasked data.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=772#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=772"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=772#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"The Hidden Layers of Dynamic Data Masking in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"]},{"@type":"Person","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/68bb31b454bafe9e139183ed4f3e9082","name":"Yvonne Vanslageren","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/22c274a7a354b81a0a8dc5b23e8e0be9bdd81a6bc0541474cfb6b954d6bb2089?s=96&d=mm&r=g","caption":"Yvonne Vanslageren"},"url":"https:\/\/www.sqltabletalk.com\/?author=2"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/772","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=772"}],"version-history":[{"count":3,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/772\/revisions"}],"predecessor-version":[{"id":785,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/772\/revisions\/785"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=772"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=772"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=772"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}