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

SQL Server in Azure

There are two flavors of SQL Server in Azure: one is the traditional SQL Server platform hosted in Azure virtual machines, also described as Infrastructure as a Service (IaaS). The other is called Azure SQL Database, database-as-a-service or Platform as a Service (PaaS).  there are several important differences in functionality and features, so let’s look at them:

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