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,

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

Answer 1

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 ('[email protected]')
      ,('[email protected]')
      ,('[email protected]');

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.