Here is the code to reproduce the issue: CREATE TABLE [dbo].[EmployeeDataMasking]( [RowId] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NULL, [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], ) ON [PRIMARY] GO Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName) VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald') -- Partial data masking works correctly here Select EmployeeId, LastName, FirstName From dbo.EmployeeDataMasking -- Partial data masking does not work correctly here. Full masking is used. Select EmployeeId, ISNULL(LastName,'') as LastName, ISNULL(FirstName,'') as FirstName From dbo.EmployeeDataMasking It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior? Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results: Select EmployeeId, COALESCE(LastName,'') as LastName, COALESCE(FirstName,'') as FirstName From dbo.EmployeeDataMasking Using Azure SQL Database

I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server. The problem that I have is that I have a system database user that handles API calls to the application that these DBs are a part of. We'll call the user "apiuser". The user needs to be unmasked in order to function. Not every database has this user for various reasons (API is not enabled on all databases by default), so when the sproc runs, it should attempt to unmask the user only if they exist on that DB. What I came up with to do that was this:

if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;

This line works fine when run on its own as a statement. However, when run as part of a stored procedure, it does not work. There is no error, the sproc runs just fine, it just does not unmask the user. My guess is for whatever reason the sproc is not handling the if exists part right, and so skips this component. SET NOCOUNT ON; GRANT UNMASK TO systemuser1; GRANT UNMASK TO systemuser2; if exists (select name from sys.database_principals where name='ApiUser') EXEC('GRANT UNMASK TO apiuser'); ALTER TABLE Table1 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()'); ALTER TABLE Table2 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()'); ALTER TABLE Table2 ALTER COLUMN Field2 ADD MASKED WITH (FUNCTION = 'default()'); END Worth noting the other two unmasks (the other system users) work just fine. It's only the one using the IF EXISTS that does not.

Using a sql server, I have to replicate the database 'source' to the 'target'. It will be a pull subscription, ran as agent on the target. Some of the columns will have to be masked, so not to be shown to users at the 'target'. > 1.) I know how to do masking, using alter table alter column command > 2.) I know to make sure the user on the 'target' is denied unmask permissions The question I have is such: The database on the 'source' existed for a long time, and the columns are currently not masked. Do I need to specifically run alter table alter column on every table/column I need it on ? before setting up the snapshot replication publication ? Or is there an option to mask specific columns at the time the snapshot is created ?

I have created table prd.CustomerDimension CREATE TABLE [prd].[CustomerDimensions] ( [WWI Customer ID] [int] NOT NULL, [Customer] [nvarchar](100) NOT NULL, [Bill To Customer] [nvarchar](100) NOT NULL, [Category] [nvarchar](50) NOT NULL, [Buying Group] [nvarchar](50) NOT NULL, [Primary Contact] [nvarchar](50) NOT NULL, [Postal Code] [nvarchar](10) NOT NULL, [Valid From] [datetime2](7) NOT NULL, [Valid To] [datetime2](7) NOT NULL, [CreditLimit] [decimal](18, 2) NULL, [PhoneNumber] [nvarchar](20) NULL, [FaxNumber] [nvarchar](20) NULL ) WITH ( DISTRIBUTION = REPLICATE, CLUSTERED COLUMNSTORE INDEX ) GO Trying to apply Mask on column PhoneNumber using below query: ALTER TABLE [prd].[CustomerDimensions] ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION = 'default()'); The query gets executed successfully but when i fetch data from the table it shows original data only no masking is applied. Even applied mask function while creating table but still not able to mask column. Please help me to figure this out.

I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view. create unique clustered index [IX_Name] on dbo.vw_ViewName(SomeUniqueId) I get this error: > The index on view 'dbo.vw_ViewName' cannot be created > because the view is referencing table 'dbo.TableName' with masked > columns. In another environment, where masking is not enabled, the index is created successfully. I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index. Here is some SQL to reproduce the issue: drop view if exists dbo.vw_Aggregate drop table if exists dbo.MainTable, dbo.SecondaryTable go create table dbo.MainTable ( MainTableId uniqueidentifier primary key, SomeExternalId uniqueidentifier, SecondaryTableId uniqueidentifier ) go create table dbo.SecondaryTable ( SecondaryTableId uniqueidentifier primary key, CreatedOn datetime, Amount decimal(19, 8), -- the below column produces error, -- if commented out - there is no error [Description] nvarchar(max) masked with (function = 'default()'), Dummy int ) go create view dbo.vw_Aggregate with schemabinding as select AggregateId = m.MainTableId, m.SomeExternalId, s.CreatedOn, s.Amount from dbo.MainTable m inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId go create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)] on dbo.vw_Aggregate(AggregateId) go

How to grant unmask permission to a user for one particular table and to one particular column within a table? For example, if in table1 if column1 and column2 is masked and in table2 if column3 and column4 is masked, for user usr1 with select Access if i want to provide unmask only to table1 and only column1 how to do so?

I need to mask the data in the following table. For example, I need to mask the first_name of the user Roberto to something like NAME>, So the end result should look like NAME1 Is this possible in SQL Server? My SQL Server details below. I have tried these methods from the documentation but still no luck [from documentation](https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15#defining-a-dynamic-data-mask)

You can reproduce the issue here: CREATE TABLE [dbo].[EmployeeDataMasking]( [RowId] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NULL, [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL, CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED ( [RowId] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], ) ON [PRIMARY] GO Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName) VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald') Select EmployeeId, LastName, FirstName, LastName + ', ' + FirstName From dbo.EmployeeDataMasking Notice the LastName and FirstName fields are partially masked (as expected). However, the combined name field contains the default mask. I don't know if this is considered a bug. However, I would think the combined field would retain the mask of the two fields it comprises. At least that's what I would prefer, since I don't know how to provide a mask for the combined field.

I am interested in using SQL Server dynamic data masking on a subset of rows within a table. Is it possible to do this? As an example, I have a table of users with email addresses. I do not want to mask records for my company's internal users. I would like to use a where clause such as where email not like '%acme.com'. After reviewing the responses, I realized that I need to clarify further. I would like for all non-administrative users to see the same query results. When they query the users table, the resulting rows with an email of acme.com should not be masked and the resulting rows with a different email domain should be masked.

We want to provide developers in our organization masked data from production to help troubleshoot production issues. What would be the best way to approach it? I've read this article https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15 But it seems to take a different approach from what I've envisioned. My idea was to have a separate database server that is replicated from the production database, and somehow mask the data during replication so that the real data will never reach the replicated database server. That way we don't have to have special security considerations about how to store and treat the replicated database server. Is it a reasonable approach?

I have read about Dynamic Data Masking and one question is still in my mind. I am wondering what will happen is such scenario: 1. Dynamic Data Masking is set up on table 2. ETL process is loading new rows using NOLOCK hint (read uncommited) 3. During data load, execute select statement with read uncommited isolation level to get "dirty read" rows. Does this "dirty data" will masked or not?

I am currently working on a project to mask and de-identify data and wanted to pass my findings through a sanity check (you guys). Is it better to create a new view which houses masked data and limits source table access to admins, or is it better to create a trigger / stored procedure to apply the masking realtime after query results have been fetched? With the view approach I would have to make some code changes, as the code base is accessing tables through a traditional ORM. But this would mask data regardless of access (app, cli, after a snapshot). Is it possible through triggers / stored procedures to intercept a request against a sensitive table, and apply the mask before returning the results? This would avoid having to make any code changes. Or would it be better to implement a hybrid solution, to add a trigger on a sensitive table, save the data as given, but then apply a stored procedure to store the same (but masked and de-identified) data in the view. Thanks in advance.

Can anyone help me explain on what basis 'Data Migration Assessment' features recommendation suggests for Dynamic Data Masking and displays certain objects/tables columns for masking. On what basis DMA identifies those columns for vulnerabilities. Is there any set algorithm for identification or is it simply based on the name of the column? Please comment in case of any more details or clarification required for the question.

I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example: email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL I give the partner the credentials for logging in to the test copy of the database as a certain user, without unmask permission. I understand this is reasonably safe. Now, we are considering sharing the database backup with our partners, so they can reload in their own server and environment. I would probably need to have contained users, so that partner cannot create new users or connect them to existing logins (not sure about this). CREATE USER user_name WITH PASSWORD = 'strong_password'; Is there a way that I can share a database backup, and still trust that the masked columns are safe, or do I have to physically delete the data?

In order to mask data I am using REMAP_DATA parameter in Oracle datapump as follows: expdp \'/ as sysdba\' DIRECTORY=DUMP_DIR DUMPFILE=dumpfile.dmp LOGFILE=logfile.log SCHEMAS=user0 REMAP_DATA=user0.table1.col1:user0.pckg_mask.fun_mask REMAP_DATA=user0.table2.col2:user0.pckg_mask.fun_mask REMAP_DATA=user0.table1.col3:user0.pckg_mask.fun_mask This is just an example. Instead of creating and deploying individual functions for different columns I am using fucnction overload method hence using the same function name but with different input parameter datatype. But some columns need special logic for masking and the logic is stored in a central table. **So, is it possible that I can know which column is being processed by datapump and from which column it will take export next? (So that I can deploy the logic at run-time for that column which is being exported currently)** The question might be vague or complicated. Please do ask for needed information or any suggestion for improving question quality.

I'm thinking of using the new dynamic data masking that has been introduced with SQL Server 2016 as part of our GDPR project . Does anyone else have experience of it? I am particularly interested if there is a performance overheard.

In SQL Server 2016 SP1 standard edition we can use dynamic-data-masking . The masking of data is controlled using the built-in security, for example: REVOKE UNMASK TO user_who_cannot_see_senstive_data Of course, this is not working for the users who are members of the db_owner database role. Is there a list with security roles or cases showing when the REVOKE UNMASK is going to work? I have found a database engine permissions but it's too complicated and does not seem to show when data cannot be masked from a user.