DacFx icon indicating copy to clipboard operation
DacFx copied to clipboard

Support UNMASK at column level

Open simonsabin opened this issue 1 year ago • 0 comments

  • SqlPackage or DacFx Version: SQL Server Data Tools 17.0.62110.20190

Steps to Reproduce:

  1. create a table with masking
  2. create user
  3. attempt to grant unmasking at column level
CREATE SCHEMA Data;
GO

-- table with masked columns
CREATE TABLE Data.Membership(
    MemberID        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    FirstName        varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    LastName        varchar(100) NOT NULL,
    Phone            varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
    Email            varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    DiscountCode    smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL
    );
GO
CREATE USER MaskingTestUser WITHOUT LOGIN;  
Go
GRANT UNMASK ON Data.Membership(FirstName) TO MaskingTestUser

Did this occur in prior versions? If not - which version(s) did it work in? Has never worked

actual result

Error on the GRANT line

Severity	Code	Description	Project	File	Line	Suppression State
Error		SQL70509: Column list cannot be specified for object-level permissions.	DBTesting	C:\Users\SimonSabin\source\repos\DBTesting\DBTesting\Table1.sql	28	

expected results

package compiles and deploys

simonsabin avatar Jul 14 '22 16:07 simonsabin