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?