SQL: UNMASK is now granular in Azure SQL

Dynamic Data Masking was added to SQL Server back in 2016. I’ve almost never used it since. Why? There were two reasons:

#1: The first reason was that it didn’t work in conjunction with other security features. In particular, you couldn’t use dynamic data masking in conjunction with Always Encrypted. From the start, I’ve been telling the product group that this is a misstep. There’s very little data that I’d want to hide from my own staff, that I’m happy still being visible to a hosting provider.

This is still an issue today. Some users might just want to use Dynamic Data Masking though, without Always Encrypted, so there might be a use case there, particularly with on-premises systems.

#2: (And this was the big one) UNMASK wasn’t granular enough. There was a single UNMASK permission for the whole database. That means it worked nothing like other permissions in the database. We can usually set permissions on schemas (my favourite), tables, and columns. And I didn’t see UNMASK as any different. Again, a number of us made that very clear to the product team.

However, this has now changed !

I wanted to call attention to a recent blog post that just mentioned in passing that UNMASK was now granular. In fact, you can apply it like before, to the whole DB, but you also apply it at the schema, table, and/or column levels.

Well at least you can in Azure SQL Database (and Synapse Analytics). One of the things I love about working with Azure SQL Database is being at the front of the queue when T-SQL and DB enhancements are made.

This is great news. Thank you to the product team !

2021-03-23