locked
Dynamic data masking from leaf to subscription not working as expected in MDS 2016 RRS feed

  • Question

  • Hi,

    While doing PoC for dynamic data masking in SQL 2016, We came across an issue, where we are trying to process data after applying mask function in one of the attribute in leaf table. When we process leaf data to subscription view as admin user, subscription procedure (UDF) reading mask data instead of original data & processing the same in MDS subscription.

    Ideally if we are running MDF leaf UDF as admin user, it should process original data not masked one.

    Is it MDS bug or Is there any other way to process the same?

    Thanks,

    Dipendra Singh Baghel


    • Edited by DipendraSB Thursday, October 13, 2016 4:26 AM
    Wednesday, October 12, 2016 5:14 AM

Answers

  • Thanks Ryan for your valuable response!

    But it doesn't seems to be working as expected. We found that by default each UDF is executing as - mds_schema_user, so after making this user as db_owner it is working as expected.

    • Proposed as answer by Charlie Liao Tuesday, October 25, 2016 5:35 AM
    • Marked as answer by Charlie Liao Tuesday, October 25, 2016 6:22 AM
    Tuesday, October 18, 2016 11:55 AM

All replies

  • You may need to grant the unmask permission to the administrative user on the database

    USE [your_database];
    GO
    
    GRANT UNMASK TO admin_user;

    You can test it by executing the UDF as the admin user:

    EXECUTE AS USER = 'admin_user';  
    SELECT /* ... */
    REVERT;
    Friday, October 14, 2016 2:42 PM
  • Thanks Ryan for your valuable response!

    But it doesn't seems to be working as expected. We found that by default each UDF is executing as - mds_schema_user, so after making this user as db_owner it is working as expected.

    • Proposed as answer by Charlie Liao Tuesday, October 25, 2016 5:35 AM
    • Marked as answer by Charlie Liao Tuesday, October 25, 2016 6:22 AM
    Tuesday, October 18, 2016 11:55 AM