SQL Server 2016 Security

Dynamic Data Masking – Only Results Set is affected

Row Level Security, cannot be applied to a view.

Logic is held in the database, apps do not need to change

Predicate function on a table – User Defined Inline TVF 1 applied to 1 table only

Security Predicate

SecurityPolicy hides rows

Best Practice is to create a schema on top of the function

Creation function in a seperate schema

Create function fn_getsalaray (@salary) returns table with schemabinding


Select 1 As fn_GetSalary_Result

where @salary < 100 and USER_NAME() = ‘dave’

OR @salary > 100 and USER_NAME() = ‘dave2’

Create Security Policy Salary Fiter

ADD FILTER PREDICATE Security.fn_getsalary(salary)

ON dbo.Salary


EXECUTE (‘Select * from Salary’) AS USER ‘User1’

Create 1 Security Policy on 1 Table




Row Level Securiy incompatible with database export using Data Tier Application Framework

Security Policies cannot target views

DBCC Shows_Statistics reports statistics on unfiltered data

Always Encrypted

Client Side Keys – keys not sent to data server – along handles man in the middle

Microsoft say no overhead for Always Encrypted!

Client driver encyrpts – Random

Only encrypted data on the server

New TDE in Trial Mode in Azure portal.azure.com – New Portal

TDE (Preview)

Create database encyption key with Algorithm AES_256

Alter database MYDB set encryption on


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s