· 6 years ago · Jun 15, 2019, 07:16 AM
1-- Why does using Dynamic Data Masking with and insert
2-- from a stored procedure fail?
3/*
4-- I am on SQL 2016
5SELECT @@VERSION
6Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64)
7 Jan 10 2019 18:51:38
8 Copyright (c) Microsoft Corporation
9 Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )
10
11*/
12
13
14-- Because of testing we cannot patch for another month
15
16USE master
17DROP DATABASE IF EXISTS dbTest
18GO
19
20-- Create a database to test
21CREATE DATABASE dbTest
22GO
23USE dbTest
24GO
25
26-- Create a table to test
27CREATE TABLE [dbo].[Person](
28 [SSN] [varchar](11) MASKED WITH (FUNCTION = 'default()') NULL
29) ON [PRIMARY]
30GO
31
32-- Prove that in insert would work when not from a stored proc
33INSERT INTO PERSON (SSN)
34SELECT SSN FROM PERSON
35GO
36-- the command succeeds
37
38-- Now make a stored procedure to test this
39CREATE OR ALTER PROCEDURE InsertdbTest
40AS
41BEGIN
42SELECT SSN FROM PERSON
43END
44GO
45
46-- try the stored procedre and i get a STACK DUMP
47INSERT INTO PERSON(SSN)
48EXEC InsertdbTest
49GO