1

For a table which has dynamic data masking enabled if I try generating (create table) scripts from SSMS (13.0.1711.0) it is not scripting the MASKED function details. Is there any other way to do it?

Sample table

CREATE TABLE TestTable 
(
    Sno INT IDENTITY(1, 1) PRIMARY KEY
    ,FirstName VARCHAR(50) NOT NULL
)
GO

ALTER TABLE TestTable ALTER COLUMN FirstName varchar(50) 
MASKED WITH (FUNCTION = 'default()');
    GO

Currently this is the output I get via SSMS - Generate Scripts

CREATE TABLE [dbo].[TestTable](
    [Sno] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [Sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Update:

  1. Applied CU2 for Microsoft SQL Server 2016 RTM (KB3182270) and now SQL Server 2016 build number is 13.0.2164.0

  2. SSMS Versions is shown as 13.0.15900.1 (Even have tried downloading the SSMS once again as suggested by Aaron Bertrand and reinstalling it)

Still the original problem is not resolved - Scripting the table gives the same result as original posted.

2

You can use Powershell and SMO.

Create a new folder ('Scripts') on your C-Drive for use in the following example

In SSMS, right-click on the 'Tables' node and select 'Start Powershell'. Paste the following code into the Powershell window

Remove-Item C:\scripts\z.sql
$scriptingOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
#$scriptingOptions.ScriptDrops = $true
$scriptingOptions.NoCollation=$True;
$scriptingOptions.ScriptBatchTerminator = $true
$scriptingOptions.NoCommandTerminator=$false
$ScriptingOptions.FileName = "C:\scripts\z.sql"
$ScriptingOptions.ToFileOnly=$true
$ScriptingOptions.AppendToFile=$true
dir | Where-Object {$_.name -like "*test*"} | foreach {$_.Script($scriptingOptions)}

Check for the z.sql file in the C:\Scripts folder

If it's any consolation - I too cannot get SSMS to natively script the masking details and I'm on the latest release.

2

I tried your exmaple with my SSMS 17.4.

enter image description here

and I can script out your test table with mask function included as shown below

/****** Object:  Table [dbo].[TestTable]    Script Date: 2018/02/21 4:27:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
    [Sno] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'default()') NULL,
PRIMARY KEY CLUSTERED 
(
    [Sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
1

Community wiki answer:

Try the latest SSMS version which is fully compatible with SQL Server 2016.

It will install alongside your old version, not upgrade it, so make sure you then open the correct version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.