Dynamic Data Masking

Here’s a new feature that I like very much: Limiting exposure of sensitive data like user emails, phone numbers, addresses, credit card numbers and so on.

This feature has been available in the Azure SQL Database for a while and now it is included in the new SQL Server 2016 . So let’s see it in action…

Continue reading

Row-Level Security part 2 – Controlling Write Access

My previous post was focused on controlling read operations. With the latest enhancements to the row-level security, it is now possible to restrict write operations as well. This feature is currently available as a preview now generally available in Azure SQL Database V12. Let’s see how that works.

Continue reading

Row-Level Security in SQL Server and Azure

Row-Level Security (RLS) is a new feature of SQL Server 2016 and Azure SQL Database that enables data access control based on the users executing those queries: if a user isn’t authorized to access certain rows in a table then those rows are automatically filtered out by the database engine. This feature promises to simplify design and coding of applications, especially in complex multi-tenancy environments, as the access control logic is moved from the application to the database. In short, instead of writing queries like this:

CREATE VIEW vwInventory AS ... (implements security logic);
SELECT * FROM vwInventory WHERE isVisibleTo = 'Paul'

We write simple queries like this:

SELECT * FROM Inventory;

Isn’t that cool?

Continue reading

Using SSIS Lookup Transformation in ETL Packages

Lookup is one of many transformations available in SQL Server Integration Services (SSIS). Its purpose is to perform a JOIN of two datasets: input columns from a data source and columns in a reference dataset – a table, view or a result of a query. Lookup can be used in various scenarios but this post focuses on how to use it to implement a typical ETL package that performs incremental loads into a Data Warehouse.

Here’s a simplified description of how Lookup works: it tries to match each incoming record with one or more records in the reference table. If there is a match, the incoming record is sent to the match output. If there is no match, Lookup generates an error by default but it can also be configured to send the data to the no match output. There are some more considerations that will be described later on. Continue reading

The MERGE Statement – One Statement for INSERT, UPDATE and DELETE

Starting with SQL Server 2008, we can use the MERGE statement to solve some every day scenarios in more elegant ways. A short definition of this new feature would be: it allows us to write a single statement to synchronize data in two tables by executing any combination of INSERT, UPDATE and DELETE operations according to specified criteria. The result of this statement isn’t something that wasn’t possible before, but the difference is that we don’t have to write more complex stored procedures that include multiple JOINs and statements like IF EXISTS. Continue reading

Database Compatibility Level 101

Each SQL Server database has a setting called Compatibility Level that determines how T-SQL commands are interpreted and what features are supported for that database. A single SQL instance can host multiple databases with different compatibility levels, so SQL statements that work on one database might not work on the other. Each version of SQL Server introduces a new compatibility level and supports two previous levels for backward compatibility: Continue reading