SQL Server Security Fundamentals

Level: Beginner / Fundamental

Table of Contents

1. Course presentation
2. Module 2 — Getting Started with SQL Server Security

• 2.1 Introduction
• 2.2 Importance of SQL Server Security
• 2.3 Authentication, Authorization and Encryption
• 2.4 Demo: Secure database files by folder permissions
• 2.5 Demo: Securing access to the database by auditing logins
• 2.6 Best practices
• 2.7 Demo file — createDB.sql

3. Module 3 — Understanding Authentication and Authorization

• 3.1 Introduction
• 3.2 Understanding Authentication
• 3.3 Demo: Create Logins and Users for Authentication
• 3.4 Demo: Map Logins to Database Users
• 3.5 Authorization and Permissions
• 3.6 Demo: Granting and Revoking Permissions
• 3.7 Summary and best practices
• 3.8 Demo files — createproducts.sql and loginuser.sql

4. Understanding Principals, Objects, and Role Based Security

• 4.1 Introduction
• 4.2 Principles in SQL Server
• 4.3 Understanding Roles and Groups
• 4.4 Demo: Create and manage Principals with Roles
• 4.5 Demo: Add and remove Principals from Groups
• 4.6 Certificates and Asymmetric Keys
• 4.7 Demo: Certificates and Asymmetric Keys in SQL Server
• 4.8 Security Objects and Considerations
• 4.9 Demo: Granting and Revoking Permissions on Objects
• 4.10 Schema Bound Objects and security
• 4.11 Demo: Schema Bound Objects and security
• 4.12 Summary and final best practices
• 4.13 Demo files — asymmetrickeys.sql, certificates.sql, objectpermissions.sql, schemabound.sql

1. Course presentation

SQL Server security is essential to protect sensitive data, control access, and ensure regulatory compliance. This course teaches how to effectively secure SQL Server databases and protect sensitive data.

Main topics covered include:

• authentication and authorization for logins and users
• permissions for principals and objects
• The importance of roles and groups
• Securing data with certificates and encryption keys

At the end of this course, you will have the SQL Server security skills and knowledge needed to protect your database, control access, and ensure data protection.

Prerequisites: Familiarity with the basics of SQL Server and ability to write basic T-SQL queries. The course uses SQL Server 2022 and SQL Server Management Studio (SSMS).

2. Getting Started with SQL Server Security

2.1 Introduction

This first module dives into the world of SQL Server security. SQL Server security is crucial for protecting sensitive data, preventing unauthorized access, and ensuring compliance with data protection regulations.

This module covers the following topics:

• The importance of security, which forms the basis of all the concepts covered
• The security triad: authentication, authorization and encryption
• Two demonstrations:

1. Secure SQL Server files using folder permissions
2. Improve database access control via logins auditing

The course focuses on the fundamentals and is not an exhaustive guide, but aims to make learning about security as interesting and educational as possible.

2.2 Importance of SQL Server Security

Although many perceive SQL Server security as limited to user logins, it goes well beyond that. It includes not only who can access the data, but also who cannot. In summary, SQL Server security includes measures to protect databases, ensuring confidentiality, integrity, availability of data and preventing unauthorized access.

Four Key Reasons to Prioritize SQL Server Security

1. Protect sensitive information SQL Server security ensures that sensitive and confidential data is protected from unauthorized disclosure or misuse.

2. Prevent data breaches and unauthorized access By implementing robust security measures, SQL Server Security helps prevent data breaches and unauthorized access to your valuable data, reducing associated risks and potential damages.

3. Regulatory compliance SQL Server security plays a vital role in ensuring that your organization complies with industry regulations and data protection laws, minimizing legal and financial risks.

4. Maintain company reputation By implementing effective SQL Server security measures, organizations build trust and credibility with customers and partners, preserving their reputation and fostering strong business relationships.

The three main defense mechanisms

Although there are many threats, three areas cover the bulk of defense mechanisms:

2.3 Authentication, Authorization and Encryption

These three fundamental concepts form the basis of SQL Server security.

Authentication

Authentication is the process of verifying the identity of users and systems attempting to access SQL Server. It is the first line of defense against unauthorized access.

SQL Server provides two primary authentication methods:

• Windows Authentication: relies on the Windows operating system to manage user identities. It is considered more secure because it benefits from Windows’ built-in security features (password policies, account lockout, etc.)
• SQL Server Authentication: Requires users to provide a username and password specific to the SQL Server instance. This mode is necessary when Windows Authentication is not possible, especially when accessing SQL Server from unknown Windows operating systems.

Authorization

authorization determines the access level and permissions of authenticated users within a SQL Server environment. Role-Based Access Control (RBAC) is a standard method for managing authorization, where users are assigned to roles that grant them specific permissions on database objects.

Encryption

Encryption is the process of protecting data by converting it into an unreadable format, which can only be decrypted by authorized users or systems with the appropriate decryption key. Several encryption methods are available in SQL Server:

• Transparent Data Encryption (TDE): disk-level encryption
• Always Encrypted: client-side encryption
• Column-level Encryption: encryption of specific columns in a table

Concrete scenario combining the three concepts

Sarah is a sales manager and needs to access the company’s customer database in SQL Server. When Sarah logs in through her company laptop, she is authenticated through Windows Authentication against Active Directory. Once authenticated, Sarah’s permissions are checked, granting her authorization to access certain tables and sales views as read-only, based on her role. Customer credit card numbers are encrypted with Always Encrypted, so sensitive card data is protected both in the database and in transit from the database to Sarah’s application.

2.4 Demo: Secure database files by folder permissions

Context

Imagine that someone with access to SQL Server on your Windows system accidentally or intentionally damages your valuable database file. Fortunately, this can be easily prevented by securing database files with folder permissions.

Key concepts

Service Account (NT Service) SQL Server uses a service account called NT Service by default. This NT Service account, also known as Service Account, allows Windows services to interact with the operating system and other resources. It provides services with their own security context and permissions, separate from user accounts.

Demonstration steps

1. SQL Server Configuration Manager: Check the service account used by SQL Server (NT Service by default)
2. SQL Server Management Studio (SSMS): Log in using Windows Authentication
3. Navigate to Data Folder: Locate SQL Server data (.mdf) and log (.ldf) files
4. Secure the folder:

• Change permissions of folder containing database files
• Remove access from users who should not have access to files directly
• Ensure that only the SQL Server Service Account and administrators have access to the folder

Fundamental Principle: The location where SQL Server stores its files must be secure. Even individuals with access to SSMS or the server should not necessarily have direct access to the physical files in the database.

2.5 Demo: Securing access to the database by auditing logins

Problem: Lack of traceability by default

By default, SQL Server does not log failed login attempts. If a user knows a valid username and tries different passwords (brute force attack), this may go unnoticed.

Demonstration steps

1. Reproduce issue: Logging into SSMS and attempting to log in with an incorrect username and password multiple times
2. Check SQL Server logs:

• Expand Management folder
• Click on SQL Server Logs → Select latest log
• Notice that no details about failed login attempts appear by default

3. Enable login auditing:

• Right click on SQL Server instance → Properties
• Navigate to Security tab
• In the “Login Auditing” section, change the option from None to Failed logins only (or Both failed and successful logins for maximum security)

4. Apply changes: Restart SQL Server services to apply changes
5. Verification: Retry incorrect connections, then review new logs to see details of failed attempts

Audit options available

Recommendation: Enable auditing of both connection types. Scenarios exist in organizations where valid users have logged in and compromised data. Having a log of all connections allows for better traceability.

2.6 Best Practices

2.7 Demo file — createDB.sql

Location: 02/demos/demo2/createDB.sql

This script creates the PSDemo demo database with a data file and a log file in a custom path, then creates an Employees table and populates it with example data.

CREATE DATABASE PSDemo
ON PRIMARY
( NAME = PSDemo_data,
  FILENAME = 'D:\PS\data\PSDemo_data.mdf')
LOG ON
( NAME = PSDemo_log,
  FILENAME = 'D:\PS\data\PSDemo_log.ldf');
GO
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Department VARCHAR(50)
);
GO
INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Department)
VALUES
    (1, 'John', 'Doe', 30, 'Sales'),
    (2, 'Jane', 'Smith', 35, 'Marketing'),
    (3, 'Mike', 'Johnson', 28, 'IT');
GO

Key points of the script:

• Using ON PRIMARY to specify primary filegroup
• Explicitly specifying file paths for data location control
• Separation of data (.mdf) and log (.ldf) files for better performance and security

3. Understanding Authentication and Authorization

3.1 Introduction

This module delves deeper into the concepts of authentication and authorization presented in the previous module. It includes three practical demonstrations.

Topics covered:

• Authentication and its different types
• The often confusing distinction between login and user
• Authorization and the concept of permissions
• Three demonstrations:

1. Creation of logins and users
2. Mapping logins to database users
3. Grant and revoke permissions from logins and users

3.2 Understanding Authentication

What is authentication?

Authentication is the process of verifying the identity of users and systems attempting to access SQL Server. It is the first line of defense against unauthorized access, ensuring that only verified users and systems can interact with the database.

Authentication modes in SQL Server

SQL Server supports two main authentication modes:

Windows Authentication

• Exploits built-in user accounts and group memberships of Windows operating system
• Considered more secure because it benefits from Windows’ built-in security features: password policies, account lockout, etc.
• Recommended for most enterprise environments

SQL Server Authentication

• Requires users to provide a SQL Server instance-specific username and password
• Needed in situations where Windows Authentication is not possible (logging in from non-Windows operating systems, some third-party applications, etc.)

Logins vs Users: a fundamental distinction

Fundamental rule: logins allow you to connect to the SQL Server instance, while users determine what can be done within each database.

Authentication modes in SQL Server Management Studio

SQL Server can be configured to accept:

• Windows Authentication Mode: Windows Authentication only
• Mixed Mode (SQL Server and Windows Authentication): both types of authentication

3.3 Demo: Create Logins and Users for Authentication

Demo environment

Two SSMS windows are open side by side:

• Left window: to create logins (Windows Authentication admin account)
• Right window: to observe the effects

Step 1: Initial connection and creation of test data

Log in with the Windows Authentication (admin) account. The PSDemo database and the Employees table are already created (see createDB.sql).

Step 2: Create a SQL Server Authentication Login

1. In Object Explorer, expand the Security folder
2. Right click on Logins → New Login
3. Select SQL Server authentication (security options are enabled)
4. Enter a login name and password

Password Policy Options

Windows Local Security Policy

To view the local password policy:

• Windows Settings → Search “local security policy”
• Example parameters: Maximum password age = 42 days, Minimum password age, Minimum password length

Practical experience: If the minimum password length is set to 8 characters in Windows policy and an attempt is made to create a login with a password of 4 characters, SQL Server will refuse the creation and display a policy non-compliance error.

Step 3: Log in with the new login

1. Click Connect → Select SQL Server Authentication
2. Enter the login name and password
3. If “User must change password at next login” is enabled, SQL Server will prompt for a new password

Important behavior: After connecting, clicking on the database will cause a “database is not accessible” error. This is normal because the login allows you to connect to the server, but does not provide access to the database without a corresponding mapped user.

Step 4: Manage Login Status

By right-clicking on the login → Status, two important options appear:

• Login → Disabled: completely disables the login. Any connection attempt will be refused with an “account is disabled” message.
• Permission to connect to database engine → Deny: the account is enabled but cannot connect to the SQL Server engine

Note on login types

• Windows Authentication Login: All password policy options are disabled because Windows manages security
• SQL Server Authentication Login: Password policy options are configurable

3.4 Demo: Map Logins to Database Users

Context

In the previous demonstration, connecting with login created an error when exploring the database. This demo explains how to create a user and map it to the login.

Demonstration steps

1. Navigate to database → Security folder → Users folder
2. Observe the four default users present in any SQL Server database
3. Right click on Users → New User
4. Keep default settings for User type (user mapped to a login)
5. Enter the name of the user and provide the name of the login (by typing directly or via the search window with the three dots ...)
6. Select the login to map
7. Click OK

Default Schema

When creating a user, it is necessary to select a default schema:

• The default schema serves as the primary namespace in which the user operates
• When a SELECT command is executed without specifying a particular schema, SQL Server assumes that the user is referring to an object in its default schema
• For example, if a user with a default schema of dbo executes SELECT * FROM Products, SQL Server will look for the dbo.Products object

Keep in mind: The default schema (dbo in most cases) is the namespace that SQL Server will automatically use when no schema is explicitly specified in that user’s queries.

3.5 Authorization and Permissions

What is authorization?

authorization is the process that determines what an authenticated user can do within a SQL Server environment. It involves assigning permissions that control access to various SQL Server resources such as databases, tables, and stored procedures.

Effectively managing authorization ensures that users and systems can only perform actions to which they are entitled, reducing the risk of accidental data loss or malicious activity.

Permission Types in SQL Server

SQL Server provides three main types of permissions:

Concrete scenario: Sarah, the sales manager

Sarah is an IT professional with a user account in the Sales database, associated with her login. This user account defines its permissions within this specific database.

Permissions granted to Sarah:

• Specific permissions on the Products table: reading and modifying data
• Permission to execute the GetProds stored procedure to retrieve specific information

Restriction:

• The Royalty table in the Sales database: Sarah has no permissions on this table
• It cannot therefore perform SELECT, UPDATE or DELETE on the Royalty table

Conclusion: This user-based separation of permissions allows fine-grained control of data access, ensuring that users can only interact with database objects for which they are authorized.

3.6 Demo: Granting and Revoking Permissions

Overall scenario

This demo combines all the concepts (logins, users, permissions) into a complete story. It shows how to grant and revoke permissions for logins and users, first via SSMS, then via T-SQL.

Why couldn’t the user see the table?

In the previous demo, the user was mapped to the login but could not see the table. This is because the user did not have the necessary authorization.

Grant permissions via SSMS

1. Right click on login → Securables
2. Click on Search → Select the Tables object type
3. Click on Browse to see the available tables → Select the desired table
4. The permissions window appears with three columns:

5. Check the box in the Grant column for Select → Click OK

Behavior of UPDATE without SELECT permission

Important concept: If we only grant UPDATE to a user without granting SELECT, and the UPDATE contains a WHERE clause referencing a column, SQL Server will refuse the operation with an error related to SELECT.

Explanation: If the user can perform an UPDATE WHERE product_id = 1 successfully, they will know that a product_id = 1 exists in the table. If it fails, it will know that this product_id does not exist. So the user could indirectly start reading the table row by row, violating the SELECT restriction. This is why SQL Server blocks UPDATE with WHERE on columns when SELECT is not granted.

• An UPDATE without WHERE clause will work with only the UPDATE permission
• An INSERT will work normally
• An UPDATE with WHERE clause referencing a column will fail if SELECT is not granted

Grant and revoke via T-SQL

The six T-SQL instructions presented in the demonstration correspond exactly to the operations performed via SSMS (see loginuser.sql file in section 3.8).

3.7 Summary and Best Practices

3.8 Demo files — createproducts.sql and loginuser.sql

createproducts.sql

Location: 03/demos/demo/createproducts.sql

This script creates the products table, populates it with 10 products, then performs a demo selection and update.

-- Create 'products' table
CREATE TABLE products
(
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2)
);
GO
-- Populate 'products' table with 10 rows
INSERT INTO products (product_id, 
        product_name, product_price)
VALUES 
    (1, 'Product 1', 10.00),
    (2, 'Product 2', 20.00),
    (3, 'Product 3', 30.00),
    (4, 'Product 4', 40.00),
    (5, 'Product 5', 50.00),
    (6, 'Product 6', 60.00),
    (7, 'Product 7', 70.00),
    (8, 'Product 8', 80.00),
    (9, 'Product 9', 90.00),
    (10, 'Product 10', 100.00);
GO
-- Selecting all data from products
SELECT * FROM products;
GO
-- Updating product price for product_id
UPDATE products
SET product_price = product_price+10
WHERE product_id = 1;
GO

loginuser.sql

Location: 03/demos/demo/loginuser.sql

This script demonstrates the complete cycle of creating a login, a user, granting permissions and revoking via T-SQL.

-- 1. Create the PSLogin at the server level
CREATE LOGIN PSLogin WITH PASSWORD = 'YourPassword'; -- Please replace 'YourPassword' with the actual password

-- 2. Use the PSDemo database
USE PSDemo;

-- 3. Create the PSUser for the PSLogin in the PSDemo database
CREATE USER PSUser FOR LOGIN PSLogin;

-- 4. Grant SELECT permission to the PSUser
GRANT SELECT TO PSUser;

-- 5. Grant UPDATE permission to the PSUser
GRANT UPDATE TO PSUser;

-- 6. Remove SELECT permissions from the PSUser
REVOKE SELECT FROM PSUser;

Explanation of each instruction:

4. Understanding Principals, Objects, and Role Based Security

4.1 Introduction

This module delves deeper into the concepts of principals, objects, and role-based security in SQL Server.

Module schedule:

1. Exploring principals in SQL Server and their importance
2. roles and groups, based on previous modules
3. certificates and keys (symmetric and asymmetric keys)
4. The different SQL Server objects and their permissions (tables, views, stored procedures)
5. The advantages of schema-bound objects
6. Best practices

4.2 Principals in SQL Server

Definition

A principal in SQL Server is an entity that can request resources from SQL Server such as databases, tables, or procedures. In other words, it is any entity (person, group, department) that can perform actions on SQL Server.

Two types of Principals

Server Principals (Logins)

• Have server-level permission scope
• Responsible for managing permissions not specific to a particular database
• Examples: SQL Server Logins, Windows Server Logins

Principals Database

• Operate in a specific database
• Responsible for managing permissions within this database
• Examples: Database Users, Database Roles, Application Roles

Key distinction

Why understand the Principals?

1. Safety SQL Server principals ensure secure access and data protection by managing permissions, controlling authentication, and enforcing security measures.

2. User management Understanding principals enables efficient user creation, role assignment, permission management, access revocation, and better user administration.

3. Authorization and Authentication Principals are required to authenticate users and authorize their actions within SQL Server.

4.3 Understanding Roles and Groups

Note: Some aspects seem repetitive because SQL Server is built around two fundamental concepts: server access and database access.

Roles vs Groups

Advantages of using Roles and Groups

Simplified permission management By assigning principals to roles, their permissions are managed efficiently. When a principal is associated with a role, it inherits the permissions of that role. This simplifies the management of permissions, allowing them to be managed at the role level rather than individually for each principal.

Security policy consistency The use of roles and groups helps reduce the complexity of the permissions model and ensure consistent application of security policies.

Default Roles in SQL Server

Database Roles:

Server Roles:

4.4 Demo: Create and manage Principals with Roles

Environment

• Database: SQLAuthority (contains Accounts and Products tables)
• SQL Server version: SQL Server 2022 Developer Edition (some features not available in Express)
• Login: PSLogin (server level)
• User: PSUser (mapped to PSLogin in SQLAuthority)

Initial PSUser permissions

• Products table only visible
• Two explicit permissions granted: Select and Update

Step 1: Checking Current Permissions

With PSLogin:

• Only the Products table is visible (limited permissions)
• SELECT and UPDATE on Products work

Step 2: Explore Default Roles

In the Object Explorer, expand the Roles folder inside the database. Each role has a particular purpose:

• db_datawriter: for users who only write data
• public: for users with minimum permissions
• db_owner: for users requiring full permissions (most powerful role)

Step 3: Create a Custom Role

Scenario: Assign a set of custom permissions to users whenever responsibilities are added or changed.

1. Right click on the Roles folder → Create a new role
2. Enter the role name (leave the owner and schema fields with the default values)
3. Add members to the role: locate PSUser and add it

Step 4: Configure Role Securables

1. Click on Securables → Search
2. Select object type Table
3. Choose the Accounts and Products tables
4. For the Accounts table: check Select in the Grant column
5. For the Products table: do not grant Select permission, but Deny on Update

Result: The user PSUser who had Update permission on Products at his own level is now denied this permission at the role level.

Fundamental rule: DENY outperforms GRANT

Principle: DENY permissions always take priority over GRANT permissions. If a user has GRANT UPDATE at their level and DENY UPDATE at the role level, it is the DENY that applies.

4.5 Demo: Adding and removing Principals from Groups

Context

This demo explores how to add and remove principals from server-level groups.

Initial problem

With limited login, attempting to modify the properties of a database (navigate to Properties → Files → modify File Growth) causes an error: the login does not have the necessary permissions to modify anything related to the database.

SQL Server Performance Tip

If File Growth is set too small, SQL Server will frequently have to grow the file, causing brief interruptions to the database, which can significantly impact performance. It is always recommended to set a higher value for File Growth, such as weekly growth.

Demonstration steps

1. Explore Server Roles:

• Navigate to instance level security folder
• Expand folder Server Roles (represents groups)
• Default roles are visible: sysadmin, serveradmin, securityadmin, etc.

2. Create a new Server Role:

• Do not use sysadmin (too many permissions)
• Right click on Server Roles → New Server Role
• Provide meaningful name

3. Configure Server Role permissions:

• Select server as Securable
• In the list of explicit permissions, select Alter any database → Grant
• This role will therefore be able to modify any database on the server

4. Add login as member:

• Navigate to Members section
• Click on Add → Display of all available logins
• Select the relevant login → OK

5. Verification:

• Return to edit database File Growth
• This time, the modification succeeds because the login now has the ALTER ANY DATABASE permission

Entity hierarchy in SQL Server

Server Roles / Groups (niveau serveur)
    └─── Logins
            └─── Database Roles (niveau base de données)
                    └─── Users

4.6 Certificates and Asymmetric Keys

Relationship between Certificates and Asymmetric Keys

In the world of SQL Server security:

• certificates act as identity cards that authenticate servers and sign code
• asymmetric keys serve as a secret code used for data encryption and decryption

These two mechanisms work together to establish a secure environment: certificates provide trust and validity to entities, while asymmetric keys guarantee the confidentiality and integrity of the data exchanged.

Certificates

certificates in SQL Server are primarily used for:

• Encryption and code signing
• Encryption of server connections and stored data
• Signing stored procedures, functions, triggers and assemblies
• Server authentication beyond username/password

Advantages of certificates:

1. Prevent unauthorized access to sensitive information by encrypting connections and data
2. Authenticate servers and sign code to ensure that only trusted entities perform specific operations
3. Provide a way to verify data integrity, ensuring that transmitted data has not been tampered with

Major advantage over Asymmetric Keys: certificates can be backed up and restored. If a certificate is lost or accidentally deleted, it can be recovered from backup.

Asymmetric Keys

asymmetric keys are mainly used for:

• Data encryption and decryption
• Digital signature

Operation:

• public key is used to encrypt data
• Corresponding private key is required for decryption
• Even if the data is intercepted, it remains unreadable without the private key

Non-repudiation: The private key can digitally sign data, allowing verification of its integrity and authenticity. Asymmetric keys also provide non-repudiation: it is impossible for the sender of a message to deny having sent it.

Critical limitation:

asymmetric keys cannot be saved in SQL Server. If lost or accidentally deleted, data encrypted with these keys is permanently unrecoverable.

Summary: When to use what?

4.7 Demo: Certificates and Asymmetric Keys in SQL Server

Part 1: Asymmetric Keys

This demo uses T-SQL (not SSMS) for all examples.

Step 1: Create a Master Key

The master key is a master key for the database. Its main purpose is to protect all other keys. It is secured with a strong password.

Step 2: Create an Asymmetric Key named CustomerKey

RSA_2048 algorithm is used. The key is asymmetric: one key for encryption, another for decryption.

Step 3: Create and populate the Customers table

Credit card numbers are initially stored in plain text (insecure practice).

Step 4: Encrypt the data

Updated CreditCard column to transform plaintext numbers to data encrypted with EncryptByAsymKey.

Step 5: Encryption Verification

SELECT on the table: credit card numbers are no longer recognizable — this is encryption in action.

Step 6: Decrypting Data

Using DecryptByAsymKey with the correct key to decrypt the data.

Step 7: Cleaning

⚠️ Warning: The cleanup step (DROP MASTER KEY, DROP ASYMMETRIC KEY) should only be performed on a demo machine, never on a production server. Losing the master key on a production server results in the permanent loss of encrypted data.

Part 2: Certificates

The certificate demo follows the same initial steps, but introduces additional functionality.

Additional steps:

1. Creating a self-signed certificate CustomerCert
2. Encryption of the CreditCard column with EncryptByCert
3. Decryption with DecryptByCert
4. Saving certificate and private key to files
5. Simulation of certificate loss: DROP CERTIFICATE
6. Impact check: decryption returns NULL (data inaccessible)
7. Restoring the certificate from backup
8. Verification: credit card numbers are accessible again

Key lesson: Certificates can be backed up and restored, unlike asymmetric keys. This is why certificates are preferable when data recovery is a must.

4.8 Security Objects and Considerations

SQL Server uses various types of objects to store and manipulate data: tables, views, stored procedures, functions and triggers. Focus on the three most commonly used objects and their safety considerations.

Tables

tables are the fundamental components of a database, containing all data. Controlling access to this data is crucial.

• Row-level security: SQL Server functionality to restrict access to specific rows in a table
• Column-level security: allows you to limit access to specific columns, ensuring data protection

Views

views are essentially saved SQL queries. They serve an important security purpose by restricting access to underlying tables.

• Permissions can be granted on a view without granting direct access to the underlying tables
• This limits the data a user can access
• views also simplify complex queries by providing a more user-friendly interface while maintaining data security

Stored Procedures

stored procedures are groups of precompiled SQL statements that can help control user operations.

• Instead of granting direct access to a table, one can create a stored procedure that performs specific operations on the table, granting the user only execute permission
• Stored procedures also contribute to security by mitigating the risk of SQL injection attacks when correctly implemented

Principle of least privilege

Definition: The Principle of Least Privilege states that users and programs should have only the minimum permissions necessary to perform their intended function.

By restricting the operations that can be performed on these objects, unauthorized users are prevented from viewing, modifying, or deleting data.

4.9 Demo: Granting and Revoking Permissions on Objects

Demonstration context

The SQLAuthority database contains:

• A Customers table (Id, Name, CreditCard)
• A CustomerView displaying all three columns
• A GetCustomer stored procedure using CustomerView
• A UserLogin login and its associated user UserName

Scenario 1: Revoke permissions on an entire table

Problem: If you want to restrict the display of credit card details for UserLogin:

• Revoke SELECT on table Customers for UserName
• The CustomerView and the stored procedure GetCustomer continue to work (they have their own permissions)

Scenario 2: GRANT + DENY combined on a specific column

-- Accorder SELECT sur la table entière
GRANT SELECT ON Customers TO UserName;

-- Refuser SELECT spécifiquement sur la colonne CreditCard
DENY SELECT ON Customers(CreditCard) TO UserName;

Result: The user can see all columns except CreditCard of the Customers table.

SQL Server treats each object independently

SQL Server maintains and manages the permissions of each object separately. A view can have different permissions than the underlying table. A stored procedure can have different permissions than the view it uses.

Advanced behavior: Object string and permissions

Experience: Modify the view definition to include all three columns (including CreditCard).

• view can see CreditCard without additional error
• Direct SELECT statement with CreditCard gives permission error
• To also restrict the view: DENY SELECT ON CustomerView(CreditCard) TO UserName

Full string: If the stored procedure is modified to retrieve all columns from the view (which itself retrieves all columns from the underlying table), the stored procedure will print CreditCard even if the direct table is restricted.

Conclusion: Managing permissions requires thinking about the entire chain of objects. Flexibility in managing permissions allows security to be refined and adjusted as needed.

4.10 Schema Bound Objects and Security

Definition

In SQL Server, a schema-bound object is an object created in a specific schema and bound to the objects it references. This link ensures that the referenced objects cannot be modified in a way that would impact the object linked to the schema.

Benefits of Schema Binding

Security use case

Create a schema-bound view that provides a restricted view of a table:

• Grant permissions to a user on the view, but not on the table
• User can only access data through view
• It cannot modify the underlying table to access additional data

Problem without Schema Binding

Without SCHEMABINDING:

1. A ViewCustomer view is created on the Customers table displaying three columns
2. An administrator drops the CreditCard column from the table with ALTER TABLE ... DROP COLUMN
3. The operation succeeds
4. Running the view now produces an error: the CreditCard column no longer exists in the table

This issue is particularly severe in a production environment with multiple developers working simultaneously.

Solution with Schema Binding

With WITH SCHEMABINDING in the view definition:

• Attempting to remove the CreditCard column from the table generates errors
• SQL Server lists all objects that depend on the table and prevents modification
• The DROP TABLE command is also blocked
• To modify the table, you must first remove the SCHEMABINDING option from the view or delete the view

4.11 Demo: Schema Bound Objects and Security

Part 1: Demonstrating the problem without Schema Binding

1. Create the Customers table in the SQLAuthority database with the columns Id, Name, CreditCard
2. Populate with data
3. Create a simple view ViewCustomer: SELECT Id, Name, CreditCard FROM dbo.Customers
4. Run view → 3 columns, 2 rows
5. Drop CreditCard column with ALTER TABLE Customers DROP COLUMN CreditCard → Success
6. Run the view again → Error: column CreditCard no longer exists

Part 2: Solution with Schema Binding

1. Recreate table and data
2. Create the view with SCHEMABINDING:

   CREATE OR ALTER VIEW ViewCustomer
   WITH SCHEMABINDING AS
   SELECT Id, Name, CreditCard
   FROM dbo.Customers;
   

Note: The dbo.Customers syntax (with the qualified schema) is mandatory with SCHEMABINDING

3. Trying to delete the CreditCard column → Multiple errors listing all dependent objects
4. Attempt DROP TABLE Customers → Same type of error

Performance benefits

SQL Server can also optimize queries based on the known structure of schema-bound objects, resulting in better performance. (Outside the scope of this security-focused module.)

4.12 Summary and Final Best Practices

4.13 Demo files — asymmetrickeys.sql, certificates.sql, objectpermissions.sql, schemabound.sql

asymmetrickeys.sql

Location: 04/demos/demo/asymmetrickeys.sql

-- Create master key 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd';

-- Create asymmetric key
CREATE ASYMMETRIC KEY CustomerKey 
   WITH ALGORITHM = RSA_2048;
   
-- Create a table to hold customer data   
CREATE TABLE Customers (
  Id INT PRIMARY KEY, 
  Name NVARCHAR(50),
  CreditCard NVARCHAR(500)
);

-- Insert sample customer records
INSERT INTO Customers (Id, Name, CreditCard) VALUES
(1, 'John Doe', '1234-5678-9012-3456'), 
(2, 'Jane Smith', '5678-1234-5678-1234');

-- Encrypt credit card data
UPDATE Customers 
SET CreditCard = EncryptByAsymKey(AsymKey_ID('CustomerKey'), CreditCard);

-- Regular select
SELECT Id, Name, CreditCard
FROM Customers;

-- Decrypt data successfully
SELECT Id, Name,
   CONVERT(varchar(100), DecryptByAsymKey(AsymKey_ID('CustomerKey'), CreditCard)) AS CreditCard
FROM Customers;

-- Cleanup
DROP ASYMMETRIC KEY CustomerKey;
DROP MASTER KEY;
DROP TABLE Customers;

Key functions used:

• CREATE MASTER KEY ENCRYPTION BY PASSWORD: creates the master key protecting all other keys
• CREATE ASYMMETRIC KEY ... WITH ALGORITHM = RSA_2048: creates an asymmetric key pair (public + private) with the 2048-bit RSA algorithm
• EncryptByAsymKey(AsymKey_ID('CustomerKey'), CreditCard): encrypts the value with the public key
• DecryptByAsymKey(AsymKey_ID('CustomerKey'), CreditCard): decrypt with private key

certificates.sql

Location: 04/demos/demo/certificates.sql

-- Create a database master key 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ngPa$$w0rd';

-- Create a self-signed certificate
CREATE CERTIFICATE CustomerCert 
   WITH SUBJECT = 'Customer Encryption';
   
-- Create a table to hold customer data   
CREATE TABLE Customers (
  Id INT PRIMARY KEY, 
  Name NVARCHAR(50),
  CreditCard NVARCHAR(500)
);

-- Insert sample customer records
INSERT INTO Customers (Id, Name, CreditCard) VALUES
(1, 'John Doe', '1234-5678-9012-3456'), 
(2, 'Jane Smith', '5678-1234-5678-1234');

-- Encrypt the credit card data with the certificate  
UPDATE Customers
SET CreditCard = EncryptByCert(Cert_Id('CustomerCert'), CreditCard);

-- Query the encrypted data
SELECT Id, Name, CreditCard FROM Customers;

-- Decrypt the data 
SELECT Id, Name,
   Convert(nvarchar, DecryptByCert(Cert_Id('CustomerCert'), CreditCard)) AS CreditCard 
FROM Customers; 

-- Back up certificate and private key
BACKUP CERTIFICATE CustomerCert
   TO FILE = 'D:\data22\CustomerCert.cert'
   WITH PRIVATE KEY (
       FILE = 'D:\data22\CustomerCert.key', 
       ENCRYPTION BY PASSWORD = 'Str0ngP@ssw0rd'
);

-- Delete the certificate and key
DROP CERTIFICATE CustomerCert;

-- Decrypt the data (will return NULL - certificate is gone)
SELECT Id, Name,
   Convert(nvarchar, DecryptByCert(Cert_Id('CustomerCert'), CreditCard)) AS CreditCard 
FROM Customers; 

-- Restore certificate 
CREATE CERTIFICATE CustomerCert
   FROM FILE = 'D:\data22\CustomerCert.cert'
   WITH PRIVATE KEY (
       FILE = 'D:\data22\CustomerCert.key',
       DECRYPTION BY PASSWORD = 'Str0ngP@ssw0rd'
);

-- Decrypt the data (now works again)
SELECT Id, Name,
   Convert(nvarchar, DecryptByCert(Cert_Id('CustomerCert'), CreditCard)) AS CreditCard 
FROM Customers; 

-- Cleanup
DROP CERTIFICATE CustomerCert;
DROP MASTER KEY;
DROP TABLE Customers;

Key functions and commands:

• CREATE CERTIFICATE ... WITH SUBJECT: creates a self-signed certificate
• EncryptByCert(Cert_Id('CustomerCert'), CreditCard): encrypt with the certificate (public key)
• DecryptByCert(Cert_Id('CustomerCert'), CreditCard): decrypt with the certificate (private key)
• BACKUP CERTIFICATE ... TO FILE ... WITH PRIVATE KEY: saves the certificate AND the private key in files
• CREATE CERTIFICATE ... FROM FILE ... WITH PRIVATE KEY: restores a certificate from backup files

objectpermissions.sql

Location: 04/demos/demo/objectpermissions.sql

USE SQLAuthority
GO

-- Create a table to hold customer data   
CREATE TABLE Customers (
  Id INT, 
  Name NVARCHAR(50),
  CreditCard NVARCHAR(500)
);

-- Insert sample customer records
INSERT INTO Customers (Id, Name, CreditCard) 
VALUES
(1, 'John Doe', '1234-5678-9012-3456'), 
(2, 'Jane Smith', '5678-1234-5678-1234');
GO

-- Create view
CREATE OR ALTER VIEW CustomerView 
AS
SELECT ID, Name, CreditCard
FROM Customers;
GO

-- Create SP
CREATE OR ALTER PROCEDURE GetCustomer
AS
SELECT *
FROM CustomerView;
GO

-- Select from table
SELECT * 
FROM Customers;

-- Execute View
SELECT *
FROM CustomerView;
GO

-- Execute SP 
EXEC GetCustomer
GO

-- Create Login 
CREATE LOGIN UserLogin 
WITH PASSWORD = 'password123';
-- Create UserName
CREATE USER UserName 
FOR LOGIN UserLogin;

-- Grant SELECT on the table
GRANT SELECT 
ON Customers TO UserName;

-- Grant SELECT on the view
GRANT SELECT 
ON CustomerView TO UserName;

-- Grant EXECUTE permission on the SP
GRANT EXECUTE 
ON GetCustomer TO UserName;

-- REVOKE SELECT permission on the table
REVOKE SELECT 
ON Customers TO UserName;

-- Grant SELECT on the table
GRANT SELECT 
ON Customers TO UserName;
-- Deny SELECT on CreditCard column
DENY SELECT 
ON Customers(CreditCard) TO UserName;
GO
-- Deny SELECT on CreditCard column in the view
DENY SELECT 
ON CustomerView(CreditCard) TO UserName;
GO

-- Drop table
DROP TABLE Customers;
-- Drop database user
DROP USER UserName;
-- Drop server login 
DROP LOGIN UserLogin;

Key concepts demonstrated:

• GRANT SELECT ON Customers TO UserName: grants SELECT permission on the entire table
• DENY SELECT ON Customers(CreditCard) TO UserName: refuses SELECT specifically on the column CreditCard
• GRANT EXECUTE ON GetCustomer TO UserName: grants permission to execute a stored procedure
• REVOKE SELECT ON Customers TO UserName: completely revokes the SELECT permission

schemabound.sql

Location: 04/demos/demo/schemabound.sql

USE SQLAuthority
GO

-- Create a table to hold customer data
CREATE TABLE Customers (
Id INT,
Name NVARCHAR(50),
CreditCard NVARCHAR(500)
);

-- Insert sample customer records
INSERT INTO Customers (Id, Name, CreditCard)
VALUES
(1, 'John Doe', '1234-5678-9012-3456'),
(2, 'Jane Smith', '5678-1234-5678-1234');
GO

-- Create a regular view (WITHOUT SCHEMABINDING)
CREATE OR ALTER VIEW ViewCustomer 
AS
SELECT Id, Name, CreditCard
FROM dbo.Customers;

-- SELECT data from View
SELECT * FROM ViewCustomer;

-- Dropping CreditCard column - SUCCESS without SCHEMABINDING
ALTER TABLE Customers
DROP COLUMN CreditCard;
GO

-- *** PROBLEM: View now fails because column no longer exists ***

-- SOLUTION: Create schema-bound view
-- Schema-bound view is dependent on base table structure

-- First recreate the table
DROP VIEW ViewCustomer;
DROP TABLE Customers;

CREATE TABLE Customers (
Id INT,
Name NVARCHAR(50),
CreditCard NVARCHAR(500)
);

INSERT INTO Customers (Id, Name, CreditCard)
VALUES
(1, 'John Doe', '1234-5678-9012-3456'),
(2, 'Jane Smith', '5678-1234-5678-1234');

-- Create schema-bound view WITH SCHEMABINDING
CREATE OR ALTER VIEW ViewCustomer 
WITH SCHEMABINDING AS
SELECT Id, Name, CreditCard
FROM dbo.Customers;  -- Must use schema-qualified name (dbo.Customers)

-- Attempt to alter base table - FAILS because view is schema-bound
ALTER TABLE Customers
DROP COLUMN CreditCard;
-- Error: Cannot DROP COLUMN 'CreditCard' because it is being referenced by object 'ViewCustomer'

-- Attempt to drop the table - Also FAILS
DROP TABLE Customers;
-- Error: Cannot drop the table 'Customers' because it is being referenced by object 'ViewCustomer'

-- Clean up (must remove view first)
DROP VIEW ViewCustomer;
DROP TABLE Customers;

Key points of the script:

• CREATE OR ALTER VIEW ViewCustomer WITH SCHEMABINDING AS: creates a view linked to the schema
• Syntax dbo.Customers (schema qualified name) is required with SCHEMABINDING
• Without SCHEMABINDING: ALTER TABLE ... DROP COLUMN succeeds, then the view fails
• With SCHEMABINDING: ALTER TABLE ... DROP COLUMN fails, protecting the integrity of the view
• To delete or modify the table, you must first delete or modify the view

Search Terms

sql · server · security · fundamentals · microsoft · databases · authentication · permissions · schema · principals · roles · asymmetric · authorization · binding · certificates · demonstration · objects · scenario · context · default · grant · groups · login · logins