SQL Server Dynamic Data Masking not working. Are you using the correct role permissions?

You have implemented the Dynamic Date Masking (DDM) in your SQL Database, but still you get the unmasked data. Are you using the correct role permissions and the correct logins?

I published a post before the general availability (GA) of DDM about what you need to know about the DDM GA.

There were two key points were highlighted about the DDM GA. They were

  • SQL Logins will be removed from the exclusion list and you have to specify the SQL users or Azure AD users directly in the exclusion list.
  • SQL users with administrative privileges are always excluded from dynamic data masking

I have mentioned those in the published article as well, the issue is at the time of that writing db_owner permissions also see the masked data, but recently I noted that db_owner can see the data unmasked. Data is only masked for the data_reader.

So if you’re seeing the unmasked data make sure, to use data_reader permissions to the users from whom you want to mask the data.

The issue is user with administrative privileges includes the db_owner as well, but I still have concerns on this, where we do not generally consider db_owner as an admin level permission.

I’m in some disucssions with the Microsoft internals regarding this change and told this change was made right before the GA. The documenations of DDM do not explain this. I will do the comments under this post if any interesting news come in.


2 thoughts on “SQL Server Dynamic Data Masking not working. Are you using the correct role permissions?

  1. Pingback: What you need to know about SQL Database Dynamic Data Masking before the GA | Thuru's Blog

  2. I got this information from Denny Cherry (www.peopletalkingtech.com )

    Data is masked for all users who don’t have the UMMASK permission granted to them. The db_owner role has the UNMASK permission, just like it has all other permissions.
    This is detailed in the SQL 2016 book that Stacia, Joey and I put on through MS Press. It’s in Chapter 2 towards the end of the chapter.

Comments are closed.