Masking column in stored procedure

Question

First of all, as an admin of SQL Server, I'm trying to use standard database for masking column.

CREATE TABLE [Person].[MyEmailAddress]
(
    [MyBusinessEntityID] [int] NOT NULL,
    [MyEmailAddressID] [int] IDENTITY(1,1) NOT NULL,
    [EmailAddress] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NULL, --<== masked
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_MyEmailAddress_BusinessEntityID_EmailAddressID] PRIMARY KEY CLUSTERED (
        [MyBusinessEntityID] ASC,
        [MyEmailAddressID] ASC
    )
) ON [PRIMARY]
GO

Then, I created a new stored procedure

CREATE PROCEDURE [Person].[Email_Address]
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * from [Person].[EmailAddress]
END
GO

Then, I created a new login user

create login AdvUserTest004
with password = 'Test123'

create user User004
for login AdvUserTest004

-- add user to the database owner role
exec sp_addrolemember N'db_owner', N'User004'

Setting the user role by referring this link to db_datawriter and db_datareader. Without these two memberships, the tables didn't appear.

Then, I tried to login with 'AdvUserTest004' with SQL Server authentication mode. Tried to execute select statement

select * from [Person].[EmailAddress]

The output result looks good, masked.

But the stored procedure above didn't appear. So, by referring this document, giving permission to execute.

Now, stored procedure appear as usual. Then, execute the stored procedure

exec [Person].[Email_Address]

The result looks like this:

So the question is,

Why is the email column unmasked when it executes in stored procedure but it is masked in select statement?
How should the email column mask when it executes in stored procedure?

gotqn · Accepted Answer · 2020-08-21T09:27:56.477

You re making your user a db_owner. If you check Chart of SQL Server Permissions:

you can see that members of this role can UNMASK any data. This can be easily check with the code below:

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [email] NVARCHAR(128) MASKED WITH (FUNCTION = 'email()')
);

INSERT INTO [dbo].[StackOverflow] ([email])
VALUES ('text1@gmail.bg')
      ,('text2@gmail.bg')
      ,('text3@gmail.bg');

SELECT [email]
FROM [dbo].[StackOverflow];

GO

DROP USER IF EXISTS [Daleman];

CREATE USER [Daleman] WITHOUT LOGIN;  
GRANT SELECT ON [dbo].[StackOverflow] TO [Daleman];  

GO

EXECUTE AS USER = 'Daleman'; 

SELECT [email]
FROM [dbo].[StackOverflow];
REVERT;

GO

EXEC sp_addrolemember N'db_owner', N'Daleman';


EXECUTE AS USER = 'Daleman'; 

SELECT [email]
FROM [dbo].[StackOverflow];
REVERT;

where I am:

creating a new table and query it (as I am sys.admin I can unmask)
creating a new user with access to query the table (who cannot unmask and see the data masked)
making the user db_owner and showing now the user can see the original data

Here is the output of the execution code:

So, I guess you are not testing correctly the case where you are seeing the data masked.

thxs for your code & explanation. Q, why do you create user without login ? I understood the rest of the code. — Daleman, Aug 21 '20 at 09:22
@Daleman for simplicity because there is no need to connect with it :-) you can test using `EXECUTE AS` to test the code. Also, when using `EXECUTE AS`, use `REVERT` in order to ensure you are with the original security context after the test is run. — gotqn, Aug 21 '20 at 09:26
And both of you should be using ALTER ROLE rather than the deprecated sp_addrolemember. — SMor, Aug 21 '20 at 11:33

score 0 · Answer 2 · answered Sep 16 '20 at 01:59

I just got example from this link

-- Demonstrate Dynamic Data Masking
-- 
-- Make sure to connect using a privileged user such as the database owner or sysadmin

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'GreatLakesUser')
BEGIN
    CREATE LOGIN GreatLakesUser 
    WITH PASSWORD = N'SQLRocks!00',
         CHECK_POLICY = OFF,
         CHECK_EXPIRATION = OFF,
         DEFAULT_DATABASE = WideWorldImporters;
END;
GO

USE WideWorldImporters;
GO

IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = N'GreatLakesUser')
BEGIN
    CREATE USER GreatLakesUser FOR LOGIN GreatLakesUser;
END;
GO

ALTER ROLE [Great Lakes Sales] ADD MEMBER GreatLakesUser;
GO

-- grant SELECT rights to role principal
GRANT SELECT ON Purchasing.Suppliers TO [Great Lakes Sales];
GO

-- select with current UNMASK rights (NOTE row count and data values), assuming you are connected using a privileged user
SELECT SupplierID, SupplierName, BankAccountName, BankAccountBranch, BankAccountCode, BankAccountNumber FROM Purchasing.Suppliers;

-- impersonate the user GreatLakesUser
EXECUTE AS USER = 'GreatLakesUser';
GO

-- select with impersonated MASKED rights (NOTE row count and data values)
SELECT SupplierID, SupplierName, BankAccountName, BankAccountBranch, BankAccountCode, BankAccountNumber FROM Purchasing.Suppliers;
GO

REVERT;
GO

-- Clean-up (optional)
/*
REVOKE SELECT ON Purchasing.Suppliers TO [Great Lakes Sales];
GO
DROP USER GreatLakesUser;
GO
DROP LOGIN GreatLakesUser;
GO
*/

Masking column in stored procedure

2 Answers2