This is the second part of a series on SQL Server Dynamic Data masking. The first part in the series<\/a> was a brief introduction to dynamic data masking, including use cases.<\/p>\n The focus of this blog will is setting up masking and some base examples.<\/p>\n Masking data is a straightforward process. Choose the table and columns to be masked, decide on the appearance of the mask, execute the masking script, and verify the results. Masking data also assumes some foundational setup tasks have been done and minimal best practices are being followed. Users that need to retrieve data that has been masked only need minimal viable security (generally Simplicity is important with security design and implementation. Simple design doesn’t imply only using the default database roles or assigning the same security to each user. Applied simplicity is like Occam’s Razor for security. It is the most straight forward security design that will support your known current and future security needs.<\/p>\n A simple security design is easy to maintain, and the security of each user is clear. This relates directly to dynamic data masking. Table selection for masking is an important task and involves working closely with the business. Data shouldn’t be restricted via masking unless necessary.<\/p>\n Remember, masking isn’t a method to implement general security, it is a supplement to existing security and a way to expose additional data in a relatively safe manner that users couldn’t ordinarily access. The most sensitive data shouldn’t be exposed even with masking, so items like social security numbers, tax IDs, etc. should still only be exposed via masking with extreme caution and to the most trusted users, if at all.<\/p>\n Choosing the appearance of the mask is also a business decision that can be guided by the architecture. Selecting a standard for patterns on your project is recommended. Consistency makes it easier for users to interpret the data they are seeing, knowing that data is masked, and it also speeds the development process since the mask doesn’t need to be determined for each column, just each column type.<\/p>\n From a technical perspective the appearance doesn’t impact functionality. General guidelines for masks would be to choose something that is not a default for your unmasked columns or a mask that has meaning in your organization.<\/p>\n The examples in this series are based on the WideWorldImporters database, that is the current standard demo database from Microsoft. More information about how to acquire that database as well as to initialize<\/p>\n Adding masking to an existing table follows the standard alter syntax.<\/p>\n If you add masking to the column in your create script the syntax is very similar to that of a constraint. If you are in a devops environment with continuous deployment, near continuous deployment, or just automated deployments, this is likely the syntax needed.<\/p>\n Some column types and columns used in temporal tables can’t be masked. Microsoft documentation includes columns encrypted with Always Encrypted, Although not listed by the documentation, period columns for system-versioned temporal tables can’t be masked and there are exceptions to the computed columns. For example:<\/p>\n Trying this will cause the following error to be raised:<\/p>\n Computed columns can’t be masked directly, but the referenced columns can be masked and that will flow through to the computed column. The order that computed columns and masked columns are added to the table are important and will determine if the column can actually be masked.<\/p>\n The following shows that the referenced columns can’t be masked after the computed column is created.<\/p>\n This returns:<\/p>\n The easiest solution for this is to create the table with masking enabled in the Adding the masked columns to the computed column referenced columns at the time of creation is successful.<\/p>\n This will execute without error. Note that since altering the table to add masking isn’t possible, so if you want to add it you will need to create a new temporal table and move the history. More about this can be found in this blog about changing temporal history<\/a>.<\/p>\n After masking has been implemented verification and testing follows. Verification, or a smoke test, will require a user with limited access. A user without a login is a good choice for this verification and can be safely use in production environments. If your test team isn’t familiar with this technique or their testing tools don’t support this scenario, an actual login and user account can be created. If there is a use case for unmasked data, testing this scenario is warranted. Special attention should be paid to intersections of additional security. Row Level Security (RLS), user roles, group assigned security should all have separate test cases.<\/p>\n In comparison to RLS, which requires an access predicate and a security policy, a single statement is required for data masking. Users with Note that the sample code contains the Looking at the returned data, all of the It is interesting to note that Microsoft Azure SQL Database includes the option to mask columns via the GUI. This can be useful for test cases or smaller companies wanting to try out masking. I am a strong advocate of automated deployments, multiple environments for testing and development, and repeatability of deployments, so this wouldn’t be my first choice. It can be a quick way to test masking and is useful for administrators not familiar with SQL Server.<\/p>\n In Azure, the overview for the database shows that Dynamic Data Masking is configured. If you click into the masking box, the columns that are masked are shown. You also get the option to configure additional columns with masking.<\/p>\n This shows the columns configured with the setup scripts for this article, plus the SSN column created and masked during one of the examples in this article.<\/p>\n Scrolling down further shows columns not masked, but recommended for consideration for masking. The default mask can be added by simply clicking the button to the left of the column. The column to be masked then moves to the previous list, Masking rules, and will be added once the Save button is pressed. If more control is wanted, click the “Add mask” button at the top of the screen.<\/p>\n This opens a new window allowing you to select the schema, table, column, and specific mask to apply.<\/p>\n The column then shows up in the Masking rules table. It also needs to be saved before it is applied to the table.<\/p>\n Remember to script out the table definition and add the masking definition to your database project if you use automated deployments with SSDT. The column definition will look similar to the following when scripted.<\/p>\n Masking can be removed in a similar fashion in Azure. Select the masked column, select the Delete button, and save the changes. The same rule applies here for DevOps environments, the column should also be removed from the table script.<\/p>\n Testing will involve your regular process and full verification with your test team. Testing stories should include all tables with masking, all masked columns, masked users and users with elevated authority. If you have other layers of security, such as RLS, it is recommended to test the intersection of masking with those layers.<\/p>\n Consistency and the ability to recreate tests is an important aspect of testing. If your team uses an automated test suite, masking should be part of those tests. Test scripts can include simple Resultset with a non-privileged user.<\/p>\n Resultset using a privileged user.<\/p>\n The test team can also use the Azure dashboard to verify masking options if they have the proper authorization to view the database there. If they can’t access the database view the dashboard, they can use SSMS and SQL scripts to verify the table definition and the metadata. This will be dependent on the skills of the test team, but exploratory scripts presented later in this article can be used by the team to validate masked columns and the mask used. Scripts can also be used to validate unmasking privileges for users.<\/p>\n Testers, and administrators, can use the output from a script like the above to validate masking. The script can be modified to include column level unmasking for SQL Server 2022. It is a general pattern to assign security at a group level, so be sure to include that in your test plans.<\/p>\n In this section I will demonstrate how masks look by default, and then what you can alter them to look like using a custom mask.<\/p>\n Default masks by type follow. Other preconfigured masks include email, random, custom, and datetime. The mask displayed is a purely aesthetic preference. It has no impact on performance, security, or storage.<\/p>\n In all of the examples so far, I have set the mask to DEFAULT. These are the default values for the basic datatypes:<\/p>\n Data type<\/strong><\/p>\n<\/td>\n Default mask<\/strong><\/p>\n<\/td>\n<\/tr>\n Date<\/p>\n<\/td>\n 01.01.1900<\/p>\n<\/td>\n<\/tr>\n String<\/p>\n<\/td>\n XXXX<\/p>\n<\/td>\n<\/tr>\n Numeric<\/p>\n<\/td>\n 0<\/p>\n<\/td>\n<\/tr>\n Binary<\/p>\n<\/td>\n 0<\/p>\n<\/td>\n<\/tr>\n Datetime (SQL 2022)<\/p>\n<\/td>\n 01.23.2023 (depends on datepart masked)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n In the next code, I will add a few more masks to the data:<\/p>\n Specialized masks can be used for any column type. This is very similar to application front end development. A hard-coded character can be used, a different date format, random values or a custom string. Most common formats are represented in the specialized masks available in SQL.<\/p>\n Basic default masks, random mask, custom string and the new datetime mask are shown below. Note that the datetime mask is new to SQL 2022 and will only work in that engine or Azure SQL.<\/p>\n Sample output from default masks created above.<\/p>\n The output from this code shows you the various masks in use:<\/p>\n Note that the above example shows the Joining to Data<\/strong><\/p>\n When a primary key \/ unique constraint is masked, it can still be used for the join column. The key for this is to remember to mask every child table that references the masked column. Without masking on both tables, there is no need to unmask data and no concerns with malicious unmasking, the unmasking is already done by the database developer. If the child table isn’t masked, this is a glaring gap in the masking strategy. There is a long-standing debate on the use of natural keys for a primary key versus artificial keys. This is a clear reason to use artificial keys. The use of masking implies that there is some implicit knowledge in the column value, so an artificial key (identity or GUID), removes that implicit knowledge.<\/p>\n The following example shows an example of masking a primary key column but not the corresponding child table. The child data can be used to unmask the parent data, so design your masking carefully.<\/p>\n Output from the above script shows the masked and unmasked values. If the masked data was treated as if it were the value of the mask, you would end up with many many rows returned because every row would match the other.<\/p>\n SQL includes many methods to move data, modify data, merge data, and extract data. Basic CRUD (CReate, Update, and Delete) functionality. Masking data does not limit that functionality, but it does introduce some caveats.<\/p>\n If a user without authorization to view unmasked data tries to move that data, in any way, the data moved will still be masked, in fact, it will be permanently obfuscated in the destination. If you can only view masked data and you perform a Users with authorization to view unmasked data can perform all of these operations and retain the original, unmasked data. If a transactional, user application contains masked data, the The masked user only sees the masked version of the CustomerName column.<\/p>\n The data is still masked when it is inserted into a new table, in this instance, the temp table #Customer.<\/p>\n A privileged account is able to see CustomerName, but notice that the temp table created by the MaskedReader account only contains masked data.<\/p>\n In the new table, #Customer, the data is permanently obfuscated, even with a privileged account.<\/p>\n If a user without authorization to see masked data also has rights to update that masked data, the base data is not obfuscated. It is correct at the physical level, but masked users, even the user that inserts or updates the data, is not able to see the unmasked version of the data.<\/p>\n The first dataset returned by that batch of statements shows the unmodified version of the Configuring Masking<\/h2>\n
SELECT<\/code> privileges), no table ownership or dbo<\/code> rights. If the user owns the table or has dbo<\/code> rights, the data isn’t masked for them.<\/p>\nMask Appearance<\/h3>\n
Preparation<\/h3>\n
Script Creation<\/h3>\n
USE WideWorldImporters\nGO\n\/*\n* Add masking to sales.Customers.CreditLimit column.\n* This is the syntax for a manual ALTER statement\n*\/\nALTER TABLE Sales.Customers\nALTER COLUMN CreditLimit\nADD MASKED WITH (FUNCTION = 'default()')\nGO<\/pre>\n
USE WideWorldImporters\nGO\n\/*\n* Add masking to sales.Customers.CreditLimit column\n* during table creation.\n* This is the syntax for a devops environment using\n* SSDT\/sqlpackage.exe or other automated deployment.\n*\n* Note that previous examples add masking to \n* additional columns.\n* Each column needs to have masking functionality added\n* in a devops environment using declarative coding.\n* \n*\/\nCREATE TABLE Sales.Customers(\n\tCustomerID int NOT NULL \n CONSTRAINT PK_Sales_Customers PRIMARY KEY CLUSTERED \n\t,CustomerName nvarchar(100) \n MASKED WITH (FUNCTION = 'default()') NOT NULL\n\t,BillToCustomerID int NOT NULL\n\t,CustomerCategoryID int NOT NULL\n-- <columns skipped for size>\n\t,LastEditedBy int NOT NULL\n\t,ValidFrom datetime2(7) NOT NULL\n\t,ValidTo datetime2(7) NOT NULL\n);<\/pre>\n
Limitations<\/h3>\n
filestream<\/code> columns, COLUMN_SET<\/code> or columns part of a column set, columns used in Polybase, and computed columns (but the referenced column can be masked).<\/p>\n--Show default mask for date \nALTER TABLE Application.People \n ALTER COLUMN ValidFrom \n ADD MASKED WITH (FUNCTION = 'default()');<\/pre>\n
Msg 13599, Level 16, State 1, Line 5<\/code>Period column 'ValidFrom' in a system-versioned temporal table cannot be altered.<\/code><\/p>\n--Example showing computed columns can't be masked\n--and columns used by computed columns cant be masked\n--after the table is created\nUSE WideWorldImporters;\nGO\n--Computed column\nALTER TABLE Application.People\nALTER COLUMN SearchName\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n--Column referenced by computed column\nALTER TABLE Application.People\nALTER COLUMN PreferredName\nADD MASKED WITH (FUNCTION = 'default()');\nGO<\/pre>\n
Msg 4928, Level 16, State 1, Line 8<\/code>Cannot alter column 'SearchName' because it is 'COMPUTED'.<\/code><\/p>\nMsg 5074, Level 16, State 1, Line 14<\/code>The column 'SearchName' is dependent on column 'PreferredName'.<\/code><\/p>\nMsg 4922, Level 16, State 9, Line 14<\/code>ALTER TABLE ALTER COLUMN PreferredName failed because one or more objects access this column.<\/code><\/p>\nCREATE TABLE<\/code> statement. Then add the computed column later via an ALTER<\/code> statement, but this solution won’t work for temporal tables.<\/p>\nMsg 13724, Level 16, State 1, Line 62<\/code>System-versioned table schema modification failed because adding computed columns while system-versioning is ON is not supported.<\/code><\/p>\nCREATE TABLE [Application].[PeopleV2](\n [PersonID] [int] \n MASKED WITH (FUNCTION = 'default()') NOT NULL,\n [FullName] [nvarchar](50) \n MASKED WITH (FUNCTION = 'default()') NOT NULL,\n [PreferredName] [nvarchar](50) \n MASKED WITH (FUNCTION = 'default()') NOT NULL ,\n SearchName AS (concat([PreferredName],N' ',[FullName])) \n PERSISTED NOT NULL,\n [IsPermittedToLogon] [bit] NOT NULL,\n [LogonName] [nvarchar](50) \n MASKED WITH (FUNCTION = 'default()') NULL,\n [IsExternalLogonProvider] [bit] NOT NULL,\n [HashedPassword] [varbinary](max) \n MASKED WITH (FUNCTION = 'default()') NULL,\n [IsSystemUser] [bit] NOT NULL,\n [IsEmployee] [bit] NOT NULL,\n [IsSalesperson] [bit] NOT NULL,\n [UserPreferences] [nvarchar](max) NULL,\n [PhoneNumber] [nvarchar](20) NULL,\n [FaxNumber] [nvarchar](20) NULL,\n [EmailAddress] [nvarchar](256) NULL,\n [Photo] [varbinary](max) NULL,\n [CustomFields] [nvarchar](max) NULL,\n [OtherLanguages] AS \n (json_query([CustomFields],N'$.OtherLanguages')),\n [LastEditedBy] [int] NOT NULL,\n [ValidFrom] [datetime2](7) \n GENERATED ALWAYS AS ROW START NOT NULL,\n [ValidTo] [datetime2](7) \n GENERATED ALWAYS AS ROW END NOT NULL,\n CONSTRAINT [PK_Application_PeopleV2] \n PRIMARY KEY CLUSTERED \n(\n\t[PersonID] ASC\n),\nPERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])\n);\nWITH (\nSYSTEM_VERSIONING = ON (HISTORY_TABLE = [Application].[People_ArchiveV2])\n);\nGO<\/pre>\n
Verification<\/h3>\n
UNMASK<\/code>, ALTER<\/code> permission or dbo<\/code> permission will view unmasked data, other users will see the mask which is the default of 0. The basic select statement returns the following.<\/p>\nEXECUTE<\/code> AS<\/code> statement, which runs the query as the MaskedReader<\/code> database user. This is the user setup previously in the sample without unmasking privileges. To use the EXECUTE AS<\/code> statement, you must run the query with a user that has IMPERSONATE rights. If your current login doesn’t have proper permission, you will get the message \u201cCannot execute as the database principal because the principal “MaskedUser” does not exist, this type of principal cannot be impersonated, or you do not have permission.\u201d. Full documentation on the statement is available here: https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/statements\/execute-as-transact-sql?view=sql-server-ver16<\/a><\/p>\nUSE WideWorldImporters\nGO\nEXECUTE AS USER = 'MaskedReader'\nGO\nSELECT\n\tCustomerID\n\t,CustomerName\n\t,CreditLimit\nFROM sales.Customers\nWHERE CreditLimit IS NOT NULL;\nGO\nREVERT;\nGO<\/pre>\n
CreditLimit<\/code> values will appear to be 0.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-1-1.png\")
<\/p>\nNULL<\/code> columns return without a mask as NULL<\/code>. This piece of information may be valuable in some scenarios, so understand that it works in this way.
Read also:\u00a0
<\/strong>SQL Server security primer<\/a>
Auditing SQL Server<\/a><\/p>\nSetup in Azure<\/h3>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-2-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-3-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-4-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-5-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-6-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-7-1.png\")
<\/p>\n[DealDescription] [nvarchar](30) \n MASKED WITH (FUNCTION = 'default()') NOT NULL,<\/pre>\n
Testing<\/h3>\n
SELECT<\/code> statements and execute as a specific user account. The setup section shows creating a user without a login. Users like this can be created for testing. They are relatively safe even in production since the user changing context needs the impersonation privilege.<\/p>\nUSE WideWorldImporters;\nGO\nEXECUTE AS USER = 'MaskedReader';\nGO\nSELECT\n\tCountryID\n\t,CountryName\n\t,IsoAlpha3Code\nFROM Application.Countries\nWHERE CountryName IN ('Armenia','Bahrain','Belize');\nGO\nREVERT;\nGO\nEXECUTE AS USER = 'UnmaskedReader';\nGO\nSELECT\n\tCountryID\n\t,CountryName\n\t,IsoAlpha3Code\nFROM Application.Countries\nWHERE CountryName IN ('Armenia','Bahrain','Belize');\nGO\nREVERT;\nGO<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-8-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-9-1.png\")
<\/p>\n\/*\n* The following can be used to validate masking security for users\n* It requires a basic understanding of the security structure\n* used by the application and database.\n* It can be expanded for SQL Server 2022 to include column level masking.\n*\/\nSELECT\n\t@@SERVERNAME\tCOLLATE Latin1_General_100_CI_AS ServerName\n\t,DB_NAME()\tCOLLATE Latin1_General_100_CI_AS DatabaseName\n\t,SU.name\tCOLLATE Latin1_General_100_CI_AS UserName\n\t,SR.name\tCOLLATE Latin1_General_100_CI_AS RoleName\n\t,'Database role' COLLATE Latin1_General_100_CI_AS SecurityType\nFROM sys.database_role_members DRM\n\tINNER JOIN sys.sysusers SU\n\t\tON DRM.member_principal_id = SU.uid\n\tINNER JOIN sys.sysusers SR\n\t\tON DRM.role_principal_id = SR.uid\nwHERE SR.name IN ('db_owner')\nUNION\nSELECT\n\t@@SERVERNAME COLLATE Latin1_General_100_CI_AS ServerName\n\t,'master'\tCOLLATE Latin1_General_100_CI_AS DatabaseName\n\t,SP.name\tCOLLATE Latin1_General_100_CI_AS LoginName\n\t,ROL.name\tCOLLATE Latin1_General_100_CI_AS RoleName\n\t,'Server role' COLLATE Latin1_General_100_CI_AS SecurityType\nFROM master.sys.server_role_members SRM\n\tINNER JOIN master.sys.server_principals SP\n\t\tON SRM.member_principal_id = SP.principal_id\n\tINNER JOIN master.sys.server_principals ROL\n\t\tON SRM.role_principal_id = ROL.principal_id\nWHERE ROL.name\t\tIN ('sysadmin')\nUNION\nSELECT\n\t@@SERVERNAME COLLATE Latin1_General_100_CI_AS ServerName\n\t,db_name()\tCOLLATE Latin1_General_100_CI_AS DatabaseName\n\t,SUGR.name\tCOLLATE Latin1_General_100_CI_AS UserName\n\t,DP.permission_name\tCOLLATE Latin1_General_100_CI_AS RoleName\n\t,'Database permission'\tCOLLATE Latin1_General_100_CI_AS SecurityType\nFROM sys.database_permissions DP\n\tINNER JOIN sys.sysusers SUGR\n\t\tON DP.grantee_principal_id = SUGR.uid\nWHERE DP.permission_name\tIN \n ('ALTER ANY MASK','CONTROL','UNMASK')<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-10-1.png\")
Read also: <\/strong>SQL Server Row-Level Security deep dive<\/a><\/p>\nMasking Data<\/h2>\n
Default Masks<\/h3>\n
\n\n
\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n --Show default mask for string\nALTER TABLE Application.People\nALTER COLUMN LogonName\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n--Show default mask for numeric\nALTER TABLE Application.People\nALTER COLUMN PersonID\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n--Show default mask for date\nALTER TABLE Purchasing.PurchaseOrders\nALTER COLUMN OrderDate\nADD MASKED WITH (FUNCTION = 'default()');\nGO\n--Show default mask for binary\nALTER TABLE Application.People\nALTER COLUMN HashedPassword\nADD MASKED WITH (FUNCTION = 'default()');\nGO<\/pre>\n
Specialized Masks<\/h3>\n
--Show default EMAIL mask\nALTER TABLE Application.People\nALTER COLUMN EmailAddress\nADD MASKED WITH (FUNCTION = 'email()');\nGO\n--Random mask\nALTER TABLE Purchasing.PurchaseOrders\nALTER COLUMN SupplierID\nADD MASKED WITH (FUNCTION = 'random(1,100)');\nGO\n--Custom string\nALTER TABLE Application.People\nALTER COLUMN PhoneNumber\nADD MASKED WITH (FUNCTION = 'partial(6,\"555-555\",1)');\nGO\n--Datetime (SQL Server 2022 functionality)\nALTER TABLE Purchasing.PurchaseOrders\nALTER COLUMN ExpectedDeliveryDate\nADD MASKED WITH (FUNCTION = 'datetime(\"M\")');\nGO<\/pre>\n
EXECUTE AS USER = 'MaskedReader';\nGO\nSELECT TOP 100\n\tAP.PersonID\n\t,AP.LogonName\n\t,AP.HashedPassword\n\t,AP.EmailAddress\n\t,AP.PhoneNumber\n\t,PO.OrderDate\n\t,PO.SupplierID\n\t,PO.ExpectedDeliveryDate\nFROM Application.People AP\n\tINNER JOIN Purchasing.PurchaseOrders PO\n\t\tON AP.PersonID\t\t= PO.LastEditedBy;\nGO\nREVERT;\nGO<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-11-1.png\")
<\/p>\nPersonID<\/code> getting masked in the base table. This type of masking gap will be explored in later sections, but you wouldn’t perform masking like this without masking all references to the column also. The PersonID<\/code> can be determined by joining to another table via a foreign key and looking at the referencing column.<\/p>\nUSE WideWorldImporters;\nGO\nCREATE TABLE dbo.KeyExample (\n\tCountryCode\tnvarchar(3) \n MASKED WITH (FUNCTION = 'default()') NOT NULL \n CONSTRAINT PK_dboKeyExample PRIMARY KEY CLUSTERED\n)\nCREATE TABLE dbo.LookupExample (\n\tLookupExampleID\tint\t NOT NULL\t\n \tCONSTRAINT PK_dboLookupExample PRIMARY KEY \n CLUSTERED\t identity\n\t,SomeValue int\tNOT NULL\n\t,CountryCode\tnvarchar(3) NOT NULL\t\n\tCONSTRAINT FK_dboLookupExample_dboKeyExample\t\n FOREIGN KEY REFERENCES dbo.KeyExample(CountryCode)\n);\nGO\n--Execute as a user that can view unmasked data\n--Current user has dbo rights\nINSERT INTO dbo.KeyExample (\n\tCountryCode\n)\nSELECT\n\tIsoAlpha3Code\nFROM Application.Countries\nORDER BY IsoAlpha3Code;\n--Uses the TABLESAMPLE keyword to return semi-random rows\n--Very efficient but will not return an exact number of rows\nINSERT INTO dbo.LookupExample (\n\tSomeValue\n\t,CountryCode\n)\nSELECT\n\t--Psuedo-random numbers. Each value will differ by row. \n --RAND() function will return same value for each row.\n\tCONVERT(INT,CONVERT(VARBINARY,NEWID())) SomeValue\n\t,IsoAlpha3Code\nFROM Application.Countries\nTABLESAMPLE(10 ROWS);\nEXECUTE AS USER = 'MaskedReader';\nGO\n--Select from the masked column and the unmasked column\n--You wouldn't normally repeat the columns, but this shows\n--the gap in masking\nSELECT\n\tKE.CountryCode\n\t,LE.LookupExampleID\n\t,LE.SomeValue\n\t,LE.CountryCode\nFROM dbo.KeyExample KE\n\tINNER JOIN dbo.LookupExample LE\n\t\tON KE.CountryCode = LE.CountryCode;\nGO\nREVERT;\nGO\n--Cleanup\n--Drop this first or drop the foreign key before the \n--parent table\nDROP TABLE IF EXISTS dbo.LookupExample\nGO\nDROP TABLE IF EXISTS dbo.KeyExample\nGO<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-12-1.png\")
<\/p>\nMoving masked data to a new table<\/h3>\n
SELECT INTO<\/code>, the new table that you create will only contain masked data. If you perform an INSERT<\/code> or an UPDATE<\/code> to a different or new table, those impacted columns will contain masked data. Anything else would completely compromise the intention of masking the data.<\/p>\nINSERT<\/code> and UPDATE<\/code> operations will need to be performed by a user with the correct authorization.<\/p>\nUSE WideWorldImporters;\nGO\nEXECUTE AS USER = 'MaskedReader';\nGO\n--MaskedReader only able to see masked data\nSELECT\n\tCustomeriD\n\t,CustomerName\nFROM Sales.Customers;\n--Insert data from the base table into #Customer\nSELECT \n\tCustomerID\n\t,CustomerName\nINTO #Customer\nFROM Sales.Customers;\n--Validate that the data is still masked\nSELECT\n\tCustomerID\n\t,CustomerName\nFROM #Customer;\n--Revert back to dbo (or current user)\nREVERT;\nGO\n--Run as a user that is able to see masked data\n--Same dataset as MaskedReader (due to RLS for MaskedReader)\n--Notice that CustomerName is unmasked\nSELECT\n\tCustomerID\n\t,CustomerName\nFROM Sales.Customers\nWHERE CustomerID IN (74,84,116,431,454,488,560,821,1050,1051);\n--Temp table created by MaskedReader\n--Data in the table is masked since the user that \n--created the table could only view a masked version of CustomerName\nSELECT\n\tCustomerID\n\t,CustomerName\nFROM #Customer;\nDROP TABLE #Customer;\nGO<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-13-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-14-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-15-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-16-1.png\")
<\/p>\nUSE WideWorldImporters;\nGO\n--Show the unmodified version of Sales.Customers\n--For an ID that is available to MaskedReader via RLS\n--CustomerName = 'Tailspin Toys (Indios, PR)'\n--Run as dbo \/ privileged account\nSELECT\n\tCustomerID\n\t,CustomerName\nFROM Sales.Customers\nWHERE CustomerID = 74;\nGO\n--Allow MaskedReader to update the Sales.Customers table\n--Note that due to RLS, even with UPDATE rights \n--on the table, the account can only update rows \n--available via RLS\nGRANT UPDATE ON Sales.Customers TO MaskedReader;\nGO\n--Run as MaskedReader\nEXECUTE AS USER = 'MaskedReader';\nGO\n--Update CustomerName\n--The column is masked for this user\nUPDATE Sales.Customers\nSET CustomerName = 'Update for masked user'\nWHERE CustomerID =74;\nGO\n--Even though the user just updated the column, \n--it is still masked\nSELECT\n\tCustomerID\n\t,CustomerName\nFROM Sales.Customers\nWHERE CustomerID = 74;\nGO\n--Revert back to dbo\nREVERT;\nGO\n--Remove UPDATE rights\nREVOKE UPDATE ON Sales.Customers TO MaskedReader;\nGO\n--Show the column when queried by a privileged user\n--Note that the column is the original text - not a masked text\nSELECT\n\tCustomerID\n\t,CustomerName\nFROM Sales.Customers\nWHERE CustomerID = 74;\nGO\n--Reset the CustomerName to the original value\nUPDATE Sales.Customers\nSET CustomerName = 'Tailspin Toys (Indios, PR)'\nWHERE CustomerID = 74;\nGO<\/pre>\n
CustomerName<\/code> for CustomerID<\/code> 74, when run with a privileged account.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-17-1.png\")
<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-18-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-19-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-20-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-21-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-22-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-23-1.png\")
<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/06\/word-image-97081-24-1.png\")
<\/p>\n