locked
I cannot see masked data with Dynamic Data Masking RRS feed

  • Question

  • I have implemented dynamic data masking a few days ago, which I have a sysadmin privilege. However, after a few days, users who do not have permission were not able to see and I was asked to executed the query for them. I have found out that I am not able to see masked fields. I granted myself to see unmasked data, which I still am not able to see the unmasked data. Is this a typical scenario people run into? If a sysadmin implements dynamic data masking, would user with sysadmin privilege can be able to unmask data? How can I unmask the fields that area already masked? 

    I am using SQL Server 2016 (SP2) Enterprise Ed. 
    I also have TDE implemented on the database. 

    Thanks,


    IN~

    Thursday, July 25, 2019 5:26 PM

Answers

  • Hi In-efficient,

     

    >>I granted myself to see unmasked data, which I still am not able to see the unmasked data. Is this a typical scenario people run into?

     

    Did you use the following statement to grant UNMASK permission?  

     

    GRANT UNMASK TO TestUser;

     

    >>If a sysadmin implements dynamic data masking, would user with sysadmin privilege can be able to unmask data?

     

    I have test it in my environment. If a sysadmin implements dynamic data masking , the login with sysadmin privilega is able to view unmask data.

     

    >> How can I unmask the fields that area already masked?   

    You can use the following statement to unmask the fields that area already masked:

     

    ALTER TABLE tablename

    ALTER COLUMN columnname DROP MASKED;

     

    For more details, please refer to  https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by In-efficient Friday, July 26, 2019 3:43 PM
    Friday, July 26, 2019 2:48 AM

All replies

  • Hi In-efficient,

     

    >>I granted myself to see unmasked data, which I still am not able to see the unmasked data. Is this a typical scenario people run into?

     

    Did you use the following statement to grant UNMASK permission?  

     

    GRANT UNMASK TO TestUser;

     

    >>If a sysadmin implements dynamic data masking, would user with sysadmin privilege can be able to unmask data?

     

    I have test it in my environment. If a sysadmin implements dynamic data masking , the login with sysadmin privilega is able to view unmask data.

     

    >> How can I unmask the fields that area already masked?   

    You can use the following statement to unmask the fields that area already masked:

     

    ALTER TABLE tablename

    ALTER COLUMN columnname DROP MASKED;

     

    For more details, please refer to  https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-2017

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by In-efficient Friday, July 26, 2019 3:43 PM
    Friday, July 26, 2019 2:48 AM
  • Hi Dedmon Dai,

    Thank you so very much for the response. 

    I found out the issue: The issue was that a service account was not granted UNMASK permission and an ETL was accessing the MASKED data and inserting it into another table. So, the value was always 'xxxx'. After finding out that the svc account did not have any permission to see UNMASKED data, we granted it to read UNMASKED data and it all worked as expected. I have learned my lessons now and I am trying to be proactive now that it does not happen again.  

    Thanks once again!


    IN~

    Friday, July 26, 2019 3:43 PM