Locking Down PII Data in SQL Server (Part 2 of 2)

By:   |   Comments (2)   |   Related: 1 | 2 | > Encryption


In Part 1, we looked out how to identify PII data in your databases. In this tip we look at how to lock down PII data using SQL Server security. Protecting your data is very important and the recent publicity over the theft of Sony PSN data underscores both the economic and ethical importance of protecting personal data. The PSN breach is estimated to have cost SONY $170 million and that doesn't include legal action taken against them by users.


The U.S Department of Commerce defines Personally Identifiable Information (PII) as any information about an individual that can be used to distinguish or trace an individual's identity, such as name, social security number, date and place of birth, mother's maiden name, or biometric records; and any other information that is linked or linkable to an individual, such as medical, educational, financial, and employment information

In Part 1, I provided a quick way to identify potential PII information in your databases. In Part 2, we will build on this information to lockdown access to the sensitive PII data while still providing existing read only users access to the non PII columns.

We will achieve this by creating a DenyPII database role and explicitly DENYING SELECT on the PII columns to this role. To restrict users from accessing the PII columns we can now add their relevant user accounts or groups to this role.

In my experience the majority of user access to data is granted through the db_datareader fixed database role.

Create the DenyPII database role

The simple script below will generate CREATE ROLE statements for all your databases (set Results to Text for best formatting)

-- this script will generate the statements to create the DenyPII role
select 'USE '+name+char(10)+'CREATE ROLE [DenyPII] AUTHORIZATION [dbo]'+char(10)
from sys.databases
where state_desc = 'ONLINE'
and name not in ('msdb','tempdb','master','model')

Edit the output as required and then execute the output to create the new DenyPII database role:

creating a denyPll database role

DENY SELECT to the DenyPII database role

The script below will iterate through all online databases and build a DCL statement that will effectively DENY SELECT access to the PII columns to the DenyPII database role.

You can edit the script further to suit the nature of your own data. You can also generate am emergency rollback script by replacing the 'DENY" with 'REVOKE'. (set Results to Text for best formatting)

-- the script examines all fields for PII sounding names and generates DENY statements
EXEC sp_msforeachDB '
USE [?]
SELECT "USE "+''[?]''+char(10)+
''DENY SELECT ON [''+s.name+ ''].['' 
+object_name(o.object_id)+'']([''+c.name+''])''+'' TO [DenyPII]''+char(10)+
from sys.columns c join sys.objects o on c.object_id = o.object_id 
join sys.schemas s on o.schema_id = s.schema_id 
where o.type IN (''U'',''V'')
and (c.name like ''%mail%''
or c.name like ''%first%name%''
or c.name like ''%last%name%''
or c.name like ''%birth%''
or c.name like ''%sex%''
or c.name like ''%address%''
or c.name like ''%city%''
or c.name like ''%region%''
or c.name like ''%state%''
or c.name like ''%postal%code%''
or c.name like ''%phone%''
or c.name like ''%social%''
or c.name like ''%ssn%''
or c.name like ''%gender%'')
and db_name() not in (''msdb'',''tempdb'',''master'',''model'')'

Edit the output as required and then execute the output to grant the DENY SELECT to the DenyPII database role:

execute the output to grant the deny select to the denyPll database role

Confirm Changes

To confirm the changes we can run sp_helprotect.

to confirm run sp_helprotect

Or we can check the database role properties in SSMS by right clicking the DenyPII database role -> selecting Properties -> Securables -> Schema -> Explicit -> Column Permissions as shown below.

check the database role properties in ssms

Verification and Validation

All that is remaining is to add the relevant database users to the DenyPII database role in each database in order to restrict access. Once this is done and the user tries to SELECT from a restricted column they will receive the following message:

verify relevent database users


Using database roles is a quick and effective way to DENY access to PII data while still providing access to other operational data through the usual fixed database role methods. PII data should be regularly reviewed. Is the PII data still required to meet the business objectives? If not, then old PII data should be destroyed.

Access management of PII data held in SQL Server is just one of the many areas of concentration in a well defined information security program.

Next Steps

    sql server categories

    sql server webinars

    subscribe to mssqltips

    sql server tutorials

    sql server white papers

    next tip

    About the author
    MSSQLTips author Alan Cranfield Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

    This author pledges the content of this article is based on professional experience and not AI generated.

    View all my tips

    Comments For This Article

    Wednesday, March 7, 2012 - 4:23:44 PM - Mauricio Crespo Back To Top (16285)

    Great Job. Excellent approach.



    Senior DBA at Darden Restaurants


    Friday, June 24, 2011 - 3:12:27 PM - Abhi Back To Top (14080)

    Thanks for sharing, good one

    get free sql tips
    agree to terms