In Transact-SQL, you can write views, stored procedures, scalar and table-valued user-defined functions, and triggers. Views serve best as a layer for selecting data, although you can modify data through views as well. Views are especially useful for columns and RLS. You can grant column permissions directly; however, doing this means a lot of administrative work. You can create a view as a projection on the base table with selected columns only, and then maintain permissions on a higher granularity level (that is, on the view instead of on the columns). In addition, you cannot give row-level permissions through a predicate in the GRANT statement. Of course, you can use the same predicate in the WHERE clause of the SELECT statement of the view you are using as a security layer. You can use table-valued functions as parameterized views.

Stored procedures are appropriate for all update activity, and also for querying data. Maintaining security through stored procedures is the easiest method of administration; with stored procedures, you typically need to grant the EXECUTE permission only. You can use triggers and scalar functions for advanced security checking; for example, for validating users input.

Programmable objects refer to base tables and to each other in a kind of chain. For example, a stored procedure can use a view that selects from a base table. All the objects in SQL Server have owners. As long as there is a single owner for all the objects in the chain, you can manage permissions on the highest level only. Using the previous example, if the stored procedure, view, and base table have the same owner, you can manage permissions for the stored procedure only. SQL Server trusts that the owner of the procedure knows what the procedure is doing. This works for any DML statement (SELECT, INSERT, UPDATE, DELETE, and MERGE).

If the chain of owners between dependent objects is broken, SQL Server must check the permissions for any objects where the chain is broken. For example, if the owner of the procedure from the previous example is different from the owner of the view, SQL Server will check the permissions on the view as well. If the owner of the table is different from the owner of the view, SQL Server will also check permissions on the base table. In addition, if you use dynamic T-SQL code, concatenate a T-SQL statement as a string, and then use the EXECUTE command to execute them, SQL Server checks the permissions on all the objects the dynamic code is using. This is logical because SQL Server cannot know which objects the dynamic code is going to use until it actually executes the code, especially if you concatenate a part of the dynamic code from user input. Besides the threat of code injection, this extra checking is another reason why you should not use dynamic string concatenation in T-SQL code in production.

To start testing programmable-object-based RLS, let's create a new demo database and change the context to this database:

USE master; 
IF DB_ID(N'RLSDemo') IS NULL 
CREATE DATABASE RLSDemo; 
GO 
USE RLSDemo; 

The next step is to create four database users without logins. Three of them represent regular users from the sales department, and the fourth one represents the sales department manager. We will use the following program to create the databases:

CREATE USER SalesUser1 WITHOUT LOGIN; 
CREATE USER SalesUser2 WITHOUT LOGIN; 
CREATE USER SalesUser3 WITHOUT LOGIN; 
CREATE USER SalesManager WITHOUT LOGIN; 
GO 

The next piece of code creates a table for the employee data. This table needs RLS:

CREATE TABLE dbo.Employees 
( 
 EmployeeId   INT          NOT NULL PRIMARY KEY, 
 EmployeeName NVARCHAR(10) NOT NULL, 
 SalesRegion  NVARCHAR(3)  NOT NULL, 
 SalaryRank   INT          NOT NULL 
); 
GO 

Now let's insert some data into the dbo.Employees table. The three rows inserted represent the three regular users from the sales department. You can check the inserted rows immediately with a query. Note that the sales region for the first two users is USA, and for the third one it is EU:

INSERT INTO dbo.Employees 
(EmployeeId, EmployeeName, SalesRegion, SalaryRank) 
VALUES 
 (1, N'SalesUser1', N'USA', 5), 
 (2, N'SalesUser2', N'USA', 4), 
 (3, N'SalesUser3', N'EU', 6); 
-- Check the data 
SELECT * 
FROM dbo.Employees; 
GO 

The dbo.Customers table, created with the following code, will also need RLS:

CREATE TABLE dbo.Customers 
( 
 CustomerId   INT          NOT NULL PRIMARY KEY, 
 CustomerName NVARCHAR(10) NOT NULL, 
 SalesRegion  NVARCHAR(3)  NOT NULL 
); 
GO 

Again, let's insert some rows into this table and check them. There are two customers from the USA and two from the EU:

INSERT INTO dbo.Customers 
(CustomerId, CustomerName, SalesRegion) 
VALUES 
 (1, N'Customer01', N'USA'), 
 (2, N'Customer02', N'USA'), 
 (3, N'Customer03', N'EU'), 
 (4, N'Customer04', N'EU'); 
-- Check the data 
SELECT * 
FROM dbo.Customers; 
GO 

None of the users have been given any permissions yet. Therefore, you can read the data only as the dbo user. If you execute the following five lines of code, only the first SELECT succeeds. For the four EXECUTE commands, you get an error:

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesManager'; 

In the next step, the code creates a stored procedure that reads the data from the dbo.Employees table. It filters the rows for regular users and returns all rows for the sales department manager:

CREATE PROCEDURE dbo.SelectEmployees 
AS 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeName = USER_NAME() 
OR USER_NAME() = N'SalesManager'; 
GO 

You must give the permission to execute this procedure to the database users:

GRANT EXECUTE ON dbo.SelectEmployees 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 
GO 

Users still cannot see the data by querying the tables directly. You can test this fact by executing the following code again. You can read the data as the dbo user, but will get errors when you impersonate other database users:

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser3'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesManager'; 
GO 

Try to execute the stored procedure, once as dbo and once by impersonating each database user:

EXEC dbo.SelectEmployees; 
EXECUTE AS USER = N'SalesUser1' EXEC dbo.SelectEmployees; 
REVERT; 
EXECUTE AS USER = N'SalesUser2' EXEC dbo.SelectEmployees; 
REVERT; 
EXECUTE AS USER = N'SalesUser3' EXEC dbo.SelectEmployees; 
REVERT; 
EXECUTE AS USER = N'SalesManager' EXEC dbo.SelectEmployees; 
REVERT; 
GO 

As the dbo user, you can execute the procedure; however, you don't see any rows because the filter in the query in the procedure did not take the dbo user into consideration. Of course, the dbo user can still query the table directly. The regular users see their rows only. The sales department manager sees all of the rows in the table.

The next procedure uses dynamic SQL to read the data from the table for a single user. By using dynamic SQL, the procedure creates a broken ownership chain. The following code illustrates this process:

CREATE PROCEDURE dbo.SelectEmployeesDynamic 
AS 
DECLARE @sqlStatement AS NVARCHAR(4000); 
SET @sqlStatement = N' 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeName = USER_NAME();' 
EXEC(@sqlStatement); 
GO 

The following code is used to give the users permission to execute this procedure:

GRANT EXECUTE ON dbo.SelectEmployeesDynamic 
TO SalesUser1, SalesUser2, SalesUser3, SalesManager; 
GO 

Now, try to execute the procedure by impersonating different users, with the help of the following code:

EXEC dbo.SelectEmployeesDynamic; 
EXECUTE AS USER = N'SalesUser1' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 
EXECUTE AS USER = N'SalesUser2' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 
EXECUTE AS USER = N'SalesUser3' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 
EXECUTE AS USER = N'SalesManager' EXEC dbo.SelectEmployeesDynamic; 
REVERT; 

When you execute this as the dbo user, the execution succeeds, but you don't get any data returned. However, when you execute the procedure while impersonating other users, you get an error because other users don't have permission to read the underlying table.