Security, Permissions and Backup – SQL Server | Lesson 28

Published / updated4 Oct 2026

Module 5 · Lesson 28Intermediate40 minInterview:FMSA

Before this lesson: Execution Plans and Query Tuning – SQL Server

Introduction

The SMS database now holds children's names, dates of birth, guardians' phone numbers, and every fee payment. Two questions decide whether it is safe: who can see and change it, and can it be recovered after a disaster?

This lesson answers both. First, security: logins, users, roles, and permissions. Then backup: taking a backup, checking it, and proving it restores.

By the end of this lesson, you will be able to:

• Explain logins, users, and roles, and the difference between authentication and authorization.
• Apply least privilege with GRANT, DENY, and REVOKE.
• Create roles for the API and for reports, and test them with EXECUTE AS.
• Explain recovery models and backup types.
• Back up SchoolDb, verify the backup, and restore it as a test copy.

Why this matters

If the API connects as an all-powerful user, one SQL injection bug can read or delete everything. With least privilege, the same bug can only do what the API's role allows, and Lesson 14's protected tables cannot be hard-deleted at all.

Backups are the last line of defence against disk failure, ransomware, and the UPDATE without WHERE from Lesson 16. A backup that has never been restored is only a hope. This lesson restores one.

The concept

Example: logins, users, and roles

Term	Level	What it is	SMS example
Login	Server	Who can connect to SQL Server	SchoolApiLogin, or a Windows account
User	Database	A login's identity inside one database	SchoolApiUser in SchoolDb
Role	Database	A named group of permissions	SchoolAppRole

Authentication answers "who are you?", checked when a login connects. Authorization answers "what may you do?", checked by permissions on every statement.

Think of the school building. The gate guard checks your identity card: authentication.

Your keys decide which rooms you can open: authorization. A visitor's pass opens the office, not the exam store room.

Example: the built-in database roles

Every database has fixed roles that SQL Server creates for you:

Fixed role	Can	Use it for
db_datareader	Read every table and view	Rarely; it includes tables reports should not see
db_datawriter	Insert, update, and delete in every table	Rarely; it cannot be limited per table
db_owner	Everything in the database, including dropping it	Administrators only, never applications

They are convenient but blunt. The SMS uses its own roles, so each permission is a decision written in a script.

Example: GRANT, DENY, REVOKE

Statement	Effect
GRANT	Allows an action
DENY	Forbids an action, even if another role grants it
REVOKE	Removes an earlier GRANT or DENY

DENY always wins over GRANT. That is how SchoolAppRole can delete rows in general but never from Students.

Least privilege means giving each user only what their job needs. Reports read views; the API runs procedures and changes data; nobody uses sa, the built-in administrator, for everyday work.

Example: recovery models and backup types

Recovery model	Log backups	Restore to	Typical use
SIMPLE	Not possible	The last full or differential backup	Development and test
FULL	Needed	Any point in time	Production

Backup type	Contains	How often, typically
Full	The whole database	Daily or weekly
Differential	Changes since the last full backup	Daily
Transaction log	Changes since the last log backup	Every 15 to 60 minutes

Two numbers drive every backup plan. RPO (Recovery Point Objective): how much data the school can afford to lose, such as 15 minutes. RTO (Recovery Time Objective): how long the system may be down, such as 2 hours.

Apply it to the SMS

Step 1: create the roles

Save this as database\028-security.sql and run it:

Show database/028-security.sql

database/028-security.sql

-- Lesson 28: database roles and permissions.
-- Safe to run more than once: roles are created only if missing; GRANT and DENY can repeat.
USE SchoolDb;
GO

-- The API's role: read and write data, run procedures and functions,
-- but never hard-delete rows that use soft delete.
IF DATABASE_PRINCIPAL_ID(N'SchoolAppRole') IS NULL
    CREATE ROLE SchoolAppRole;
GO

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA::dbo TO SchoolAppRole;
DENY DELETE ON dbo.Students TO SchoolAppRole;
DENY DELETE ON dbo.Teachers TO SchoolAppRole;
DENY DELETE ON dbo.Guardians TO SchoolAppRole;
DENY DELETE ON dbo.Users TO SchoolAppRole;
DENY DELETE ON dbo.FeePayments TO SchoolAppRole;
DENY DELETE ON dbo.AuditLogs TO SchoolAppRole;
DENY UPDATE ON dbo.AuditLogs TO SchoolAppRole;
GO

-- The reports role: read the views only, never the tables.
IF DATABASE_PRINCIPAL_ID(N'ReportsReaderRole') IS NULL
    CREATE ROLE ReportsReaderRole;
GO

GRANT SELECT ON dbo.vw_StudentDetails TO ReportsReaderRole;
GRANT SELECT ON dbo.vw_CurrentEnrollments TO ReportsReaderRole;
GRANT SELECT ON dbo.fn_GetSectionResults TO ReportsReaderRole;
GO

-- Check: what each role may and may not do.
SELECT
    pr.name AS RoleName,
    pe.state_desc AS Permission,
    pe.permission_name AS Action,
    pe.class_desc AS AppliesTo,
    ISNULL(OBJECT_NAME(pe.major_id), SCHEMA_NAME(pe.major_id)) AS ObjectName
FROM sys.database_permissions AS pe
INNER JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name IN ('SchoolAppRole', 'ReportsReaderRole')
ORDER BY RoleName, Permission, ObjectName, Action;
GO

The last query lists 15 permissions. The important rows:

RoleName	Permission	Action	AppliesTo	ObjectName
ReportsReaderRole	GRANT	SELECT	OBJECT_OR_COLUMN	vw_StudentDetails
SchoolAppRole	DENY	DELETE	OBJECT_OR_COLUMN	Students
SchoolAppRole	GRANT	EXECUTE	SCHEMA	dbo
SchoolAppRole	GRANT	SELECT	SCHEMA	dbo

ON SCHEMA::dbo grants a permission on every object in the dbo schema at once, including tables added later.

Example: test the roles

CREATE USER ... WITHOUT LOGIN creates a database user that cannot connect, which is perfect for testing. EXECUTE AS USER runs statements as that user until REVERT:

Show queries/028-permissions-test.sql

queries/028-permissions-test.sql

-- Lesson 28: test the roles with users that have no login (test users only).
-- Everything runs in a transaction that is rolled back, so the test users disappear.
-- Some statements fail on purpose: read each error.
USE SchoolDb;
GO

BEGIN TRANSACTION;

CREATE USER TestApiUser WITHOUT LOGIN;
ALTER ROLE SchoolAppRole ADD MEMBER TestApiUser;

CREATE USER TestReportUser WITHOUT LOGIN;
ALTER ROLE ReportsReaderRole ADD MEMBER TestReportUser;

-- 1. Act as the API user.
EXECUTE AS USER = 'TestApiUser';

SELECT COUNT(*) AS StudentsVisibleToApi FROM dbo.Students;      -- allowed
EXEC dbo.usp_GetClassList @ClassId = 10, @SectionName = 'B';     -- allowed
DELETE FROM dbo.Students WHERE Id = 6;                          -- denied

REVERT;

-- 2. Act as the reports user.
EXECUTE AS USER = 'TestReportUser';

SELECT COUNT(*) AS StudentsInView FROM dbo.vw_StudentDetails;   -- allowed
SELECT COUNT(*) AS StudentsInTable FROM dbo.Students;           -- denied

REVERT;

ROLLBACK TRANSACTION;
GO

As the API user, reading and running the procedure work: 10,012 students, and the 10-B list. Deleting a student fails:

Output

Msg 229, Level 14, State 5, Line 15
The DELETE permission was denied on the object 'Students', database 'SchoolDb', schema 'dbo'.

As the reports user, the view returns 10,012 students, but the table is closed:

Output

Msg 229, Level 14, State 5, Line 23
The SELECT permission was denied on the object 'Students', database 'SchoolDb', schema 'dbo'.

The transaction is rolled back, so the two test users disappear. Testing permissions this way, before any application connects, catches missing grants early instead of on the first day in production.

Example: a real login for the API

In production, the API connects with its own login, mapped to a user in the role. This is the shape, run once by an administrator with a strong password from a secrets store:

Example

CREATE LOGIN SchoolApiLogin WITH PASSWORD = 'Replace-With-A-Long-Random-Secret-1!';
GO
USE SchoolDb;
GO
CREATE USER SchoolApiUser FOR LOGIN SchoolApiLogin;
ALTER ROLE SchoolAppRole ADD MEMBER SchoolApiUser;

Never write the real password in a script or in source control. Module 10 shows how the API reads its connection string from configuration and secrets.

Step 2: back up, verify, and restore

This script takes a full backup, checks it, restores it under a new name, compares the data, and drops the test copy:

Show queries/028-backup-restore.sql

queries/028-backup-restore.sql

-- Lesson 28: back up SchoolDb, check the backup, and prove it restores.
-- The backup file goes to the server's default backup folder.
-- The test restore creates SchoolDb_RestoreTest and drops it at the end.
USE master;
GO

-- 1. A full backup with checksums (INIT replaces an older file with the same name).
BACKUP DATABASE SchoolDb
TO DISK = N'SchoolDb.bak'
WITH INIT, CHECKSUM, NAME = N'SchoolDb full backup';
GO

-- 2. Check that the file is readable and complete.
RESTORE VERIFYONLY
FROM DISK = N'SchoolDb.bak'
WITH CHECKSUM;
GO

-- 3. Prove it: restore a copy under a new name, next to the existing data files.
DECLARE @DataPath NVARCHAR(260) = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(260));
DECLARE @LogPath NVARCHAR(260) = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS NVARCHAR(260));
DECLARE @Sql NVARCHAR(MAX) = CONCAT(
    N'RESTORE DATABASE SchoolDb_RestoreTest FROM DISK = N''SchoolDb.bak'' WITH ',
    N'MOVE N''SchoolDb'' TO N''', @DataPath, N'SchoolDb_RestoreTest.mdf'', ',
    N'MOVE N''SchoolDb_log'' TO N''', @LogPath, N'SchoolDb_RestoreTest_log.ldf'', ',
    N'RECOVERY, CHECKSUM;');
EXEC sys.sp_executesql @Sql;
GO

-- 4. The copy has the same data.
SELECT
    (SELECT COUNT(*) FROM SchoolDb.dbo.Students) AS StudentsOriginal,
    (SELECT COUNT(*) FROM SchoolDb_RestoreTest.dbo.Students) AS StudentsInCopy;
GO

-- 5. Remove the test copy. The backup file stays.
DROP DATABASE SchoolDb_RestoreTest;
GO

Output

Processed 10056 pages for database 'SchoolDb', file 'SchoolDb' on file 1.
Processed 2 pages for database 'SchoolDb', file 'SchoolDb_log' on file 1.
BACKUP DATABASE successfully processed 10058 pages in 1.328 seconds (59.167 MB/sec).
The backup set on file 1 is valid.
RESTORE DATABASE successfully processed 10058 pages in 1.698 seconds (46.274 MB/sec).

StudentsOriginal	StudentsInCopy
10012	10012

About 80 MB backed up and restored in seconds, and the copy matched the original exactly. The file name SchoolDb.bak has no folder, so SQL Server uses its default backup folder; on a full SQL Server install that is the Backup folder of the instance.

Example: check your recovery model

Example

SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = 'SchoolDb';

New databases copy the setting of the model database. On a full SQL Server install it is often FULL, and in FULL the transaction log keeps growing until you back it up.

On a development laptop with no log backups, switch to SIMPLE: ALTER DATABASE SchoolDb SET RECOVERY SIMPLE;. Production is the opposite: FULL, with regular log backups.

Example: a backup plan for the school

Suppose the school agrees: lose at most 15 minutes of data (RPO), and be back within 2 hours (RTO). A plan that meets both:

Backup	When	Kept for
Full	Sunday night	4 weeks
Differential	Every other night	1 week
Transaction log	Every 15 minutes	1 week

To restore to 10:40 AM on Wednesday: Sunday's full backup, Tuesday night's differential, then every log backup up to 10:40. SQL Server Agent (included in Developer, Standard, and Enterprise editions) or a managed cloud service runs the schedule.

Example: the 3-2-1 rule

A simple rule for where backups live: 3 copies of the data, on 2 different kinds of storage, with 1 copy off site, such as in a cloud storage account.

Ransomware has made a fourth idea common: keep one copy that cannot be changed or deleted for a set time. An attacker who reaches the server cannot then destroy the backups too.

Common mistakes

1. The application connects as sa or db_owner. One bug exposes everything. Fix: a dedicated login with a least-privilege role.
2. Passwords in scripts or source control. Fix: keep secrets in a secrets store or configuration that is not committed.
3. Backups on the same disk as the database. One disk failure loses both. Fix: copy backups to separate storage, ideally off site.
4. Never testing a restore. The backup may be unusable. Fix: restore to a test database on a schedule, as this lesson does.
5. FULL recovery with no log backups. The log fills the disk. Fix: schedule log backups, or use SIMPLE where point-in-time recovery is not needed.

In real projects

From 16 years of .NET projects

The worst day I have seen on a school project was a disk failure on a server whose backups were written to the same disk. Every backup was lost with the data, and the school re-entered a term of marks from paper.

Since then, backup copies leave the server the same day, and a restore test runs every week.

On security, my first question in every review is: "Which login does the application use, and what can it do?" Too often the answer is sa. Moving to a role like SchoolAppRole takes an afternoon and turns many future disasters into harmless errors.

Best practices

• Give each application and person their own login, and grant through roles.
• Grant the least privilege needed; use DENY for actions that must never happen.
• Let reporting users read views, not tables.
• Agree RPO and RTO with the school, then design full, differential, and log backups to meet them.
• Keep backups on separate storage and test restores regularly.
• Follow the 3-2-1 rule, with one copy that cannot be changed or deleted.
• Prefer your own roles over the broad fixed roles such as db_datawriter.

Recap

• A login connects to the server; a user exists in a database; roles group permissions.
• Authentication is who you are; authorization is what you may do.
• GRANT allows, DENY forbids and wins, REVOKE removes.
• SchoolAppRole can change data but never hard-delete protected rows; ReportsReaderRole reads views only.
• Backups need a verified file and a tested restore; 10,012 students came back intact.
• SIMPLE suits development; FULL with log backups suits production.
• RPO and RTO decide how often each backup type runs.

Homework

Library module task: Let the reports role read vw_AvailableBooks, without giving it access to any library table.

A correct result looks like: as a test user in ReportsReaderRole, selecting from vw_AvailableBooks works, and selecting from BookCopies is denied.

Show the solution

library-assignment/028-library-permissions.sql

-- Library homework, Lesson 28: the reports role may read the library view, not the tables.
USE SchoolDb;
GO

GRANT SELECT ON dbo.vw_AvailableBooks TO ReportsReaderRole;
GO

The view and the tables have the same owner, dbo, so SQL Server does not check table permissions when the view reads them. This is called ownership chaining, and it is what makes views a safe window onto private tables.

Quiz

1. What is the difference between a login and a user?

   1. They are the same
   2. A login connects to the server; a user is its identity inside a database
   3. A user connects; a login is inside the database
   4. Logins are only for Windows
   Show answer

   Answer: B. A login connects to the server; a user is its identity inside a database Logins authenticate at server level; users receive permissions in one database.

2. SchoolAppRole has GRANT DELETE on the schema and DENY DELETE on Students. Can it delete a student?

   1. Yes, GRANT wins
   2. No, DENY wins
   3. Only on weekends
   4. Only with EXECUTE AS
   Show answer

   Answer: B. No, DENY wins DENY always overrides GRANT.

3. Which recovery model allows restoring to a point in time?

   1. SIMPLE
   2. FULL
   3. BULK_LOGGED only
   4. None
   Show answer

   Answer: B. FULL FULL keeps the log chain, with regular log backups, for point-in-time restore.

4. What does RESTORE VERIFYONLY check?

   1. That the database is fast
   2. That the backup file is readable and complete
   3. That users have permissions
   4. That the log is empty
   Show answer

   Answer: B. That the backup file is readable and complete It checks the backup file; only a real restore proves the data comes back.

5. Which login should the SMS API use?

   1. sa
   2. A dedicated login in a least-privilege role
   3. Any developer's login
   4. The Windows administrator
   Show answer

   Answer: B. A dedicated login in a least-privilege role A dedicated, limited login limits the damage of any application bug.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is the difference between a login and a user in SQL Server? concept

A login is server-level and controls who can connect. A user is database-level, mapped to a login, and holds permissions inside one database. For example, SchoolApiLogin connects, and SchoolApiUser works in SchoolDb.

See the example · Permalink

F What do GRANT, DENY, and REVOKE do? concept

GRANT allows an action, DENY forbids it, and REVOKE removes an earlier grant or deny. DENY always wins over GRANT, which is how a role can delete in general but never delete from Students.

See the example · Permalink

F What are the full, differential, and transaction log backup types? concept

A full backup copies the whole database. A differential copies changes since the last full backup. A log backup copies changes since the last log backup and allows restoring to a point in time.

See the example · Permalink

Mid (3-5 yrs)

M What is the difference between the SIMPLE and FULL recovery models? concept

SIMPLE allows no log backups, so you can restore only to the last full or differential backup. FULL needs regular log backups, or the log keeps growing, and it allows restoring to any point in time. Use SIMPLE for development and FULL for production.

See the example · Permalink

M Why should an application not connect as sa or db_owner? concept

One bug or SQL injection would then have full control, including dropping the database. Least privilege gives the application its own login and a role with only the permissions it needs, such as running procedures and changing specific tables.

See the example · Permalink

M What are RPO and RTO? concept

RPO (Recovery Point Objective) is how much data the business can afford to lose, such as 15 minutes. RTO (Recovery Time Objective) is how long the system may be down, such as 2 hours. Together they decide the backup types and schedule.

See the example · Permalink

Senior (6-9 yrs)

S The school can lose at most 15 minutes of data. Design the backup schedule and explain a restore to 10:40 AM Wednesday. scenario

Use the FULL recovery model, a full backup on Sunday night, differentials on the other nights, and log backups every 15 minutes. To restore: Sunday's full backup, Tuesday night's differential, then every log backup up to 10:40 with STOPAT.

How to answer: Start from RPO and RTO, then derive the schedule. Walk through the restore sequence step by step, and add retention, off-site copies, and regular restore tests.

See the example · Permalink

S How do you know your backups actually work? concept

RESTORE VERIFYONLY checks that a backup file is readable, but only a real restore proves it. Restore regularly to a test server, run DBCC CHECKDB, check key row counts, and time it against the RTO.

How to answer: Distinguish verifying a file from proving a restore. Describe an automated, scheduled restore test with results someone reviews, and mention that the course's restored copy was checked by counting students.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you protect a school database against ransomware? design

Follow 3-2-1: three copies, on two kinds of storage, one off site, plus one immutable copy that cannot be changed or deleted for a set time. Limit who can reach backup storage, use separate credentials for it, and test restores from the immutable copy.

How to answer: Cover prevention (least privilege, patching), protection of backups (immutability, separate accounts), and recovery (tested restore plan meeting the RTO). Keep it business-focused: the school must be running again on the next school day.

See the example · Permalink

Resources

• Code for this lesson: git tag lesson-28-security-backup (code repository coming soon)
• Files: database/028-security.sql, queries/028-permissions-test.sql, queries/028-backup-restore.sql, library-assignment/028-library-permissions.sql
• Video: coming soon

What's next

The SQL Server part of the course ends with an interview lesson that brings Modules 2 to 5 together. Lesson 29: The SQL interview

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.