Dynamic Data masking

Sun Shine 296 Reputation points
2022-01-21T00:37:27.737+00:00

Hello:

I have a situation, where I have to configure dynamic data masking in SQL Server Database (tables & columns). Our applications users are not in Windows & not in SQL Server, but are stored outside of database system, which we call as a userstore.

I want to know how doing data masking for the users who are not as windows & sql server users ???

Any pointer or help is greatly appreciated.

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,808 questions
0 comments
Accepted answer
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-01-21T02:23:02.413+00:00

    Hi @Sun Shine ,

    Permissions for Dynamic Data masking;

    You do not need any special permission to create a table with a dynamic data mask, only the standard CREATE TABLE and ALTER on schema permissions.

    Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table. It is appropriate to grant ALTER ANY MASK to a security officer.

    Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.

    The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission.

    Refer to MS document Dynamic Data Masking.

    >how doing data masking for the users who are not as windows & sql server users ???

    If the users are not be added to SQL server(SQL logins or database users), users can not connect to SQL server databases, they all can’t access to dynamic data masking database. If users want to access Dynamic data masking databases, you need to add them to SQL server and give them the permissions that i mentioned above.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sun Shine 296 Reputation points
    2022-01-21T07:18:31.313+00:00

    Hello Cathy:

    Thanks for your reply. in your reply, you answered my question. "If the users are not be added to SQL server(SQL logins or database users), users can not connect to SQL server databases, they all can’t access to dynamic data masking database. "

    Thank you for your reply.

    0 comments
  2. Sun Shine 296 Reputation points
    2022-01-21T07:52:22.213+00:00

    Hello Cathy:

    In your reply you mentioned that If the users are not be added to SQL server(SQL logins or database users), users can not connect to SQL server databases, they all can’t access to dynamic data masking database. Can you please send me the link where it is mentioned in MS documentation as per your reply.

    Thanks