Home > Enterprise >  SQL Server :: MASKED WITH (FUNCTION = 'default()'); not working
SQL Server :: MASKED WITH (FUNCTION = 'default()'); not working

Time:11-24

My goal is to mask columns on SQL Server 2019.

I'm following a very easy guide.

I run this query against AdventureWorks2014 and I create a copy of Person.Person:

SELECT  [BusinessEntityID]
      ,[PersonType]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailPromotion]
      ,[AdditionalContactInfo]
      ,[Demographics]
      ,[rowguid]
      ,[ModifiedDate]
      INTO [Person].[PersonMasked]
  FROM [AdventureWorks2014].[Person].[PersonMasked]
  ORDER BY BusinessEntityID

I'm now going to mask the column FirstName as is written in the guide:

ALTER TABLE [AdventureWorks2014].[Person].[PersonMasked]
ALTER COLUMN FirstName NVARCHAR(10) MASKED WITH (FUNCTION = 'default()');

And I receive the error:

Msg 8152, Level 16, State 30, Line 1
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2021-11-23T15:32:43.0426983 01:00

Where am I wrong?

Where can I find the function FUNCTION = 'default()' in SSMS?

CodePudding user response:

I believe the error is related to the change of nvarchar precision to 10 rather than to the

FUNCTION = 'default()' 

If you have any data with values longer than 10 characters they are going to be truncated and this will result in loss of data.

Just do:

ALTER TABLE [AdventureWorks2014].[Person].[PersonMasked]
ALTER COLUMN FirstName NVARCHAR(50) MASKED WITH (FUNCTION = 'default()');

Helpful links:
Altering column size in SQL Server
What happens when you modify (reduce) a column's length?

  • Related