Is this a bug or I did something wrong when I created the table?

Question

I have a table in a Azure SQL Instance (12.0.2000.8) and I found this weird behaviour when I query it.

The column is defined as Integer, but it does not respond to the "WHERE" filters, also casting to varchar return weird results. It only happens in one table (so far).

What is happening here? Is a bug? Am I doing something wrong? Should I drop the table and create it again?

More info: The problem happens when I query the table. If I use different machines and clients I got the same problem (It happens with JDBC too).

The collation is SQL_Latin1_General_CP1_CI_AS

Thanks!

Answer 1

You have dynamic data masking on this table and the user has not been granted UNMASK permissions.

You're seeing masked data in the output, not the real values.

Repro:

CREATE TABLE dbo.Test 
(
    c integer
        MASKED WITH (FUNCTION = 'default()')
        NOT NULL 
);
INSERT dbo.Test 
    (c)
VALUES 
    (123);

CREATE USER Bob WITHOUT LOGIN;
GRANT SELECT ON dbo.Test TO Bob;

EXECUTE AS USER = 'Bob';

SELECT
    T.c,
    c_vc = CONVERT(varchar(11), T.c)
FROM dbo.Test AS T
WHERE
    T.c <> 0;

REVERT;

c	c_vc
0	xxxx

If we grant UNMASK, the real data becomes visible:

GRANT UNMASK ON dbo.Test TO Bob;
EXECUTE AS USER = 'Bob';
SELECT
    T.c,
    c_vc = CONVERT(varchar(11), T.c)
FROM dbo.Test AS T
WHERE
    T.c <> 0;
REVERT;

c	c_vc
123	123

Tidy up:

DROP USER Bob;
DROP TABLE dbo.Test;