16. Security & Maintenance

The Principle of Least Privilege

The foundation of database security is a rule called the Principle of Least Privilege: every user and application should have access to exactly what they need to perform their function — and nothing more. A student portal application needs to read student records and update enrollments. It does not need to drop tables, modify stored procedures, or access salary data. Granting it only what it needs limits the damage if the application is ever compromised.

SQL Server implements this through a layered permission system built around three objects: Logins, Users, and Roles.

Logins — Server-Level Access

A Login is a credential that grants access to the SQL Server instance itself. Before anyone can connect to the server and use any database, they need a login. SQL Server supports two types.

A Windows Login uses an existing Windows user account or group. Authentication is handled by Windows, so no password is stored in SQL Server. This is the most secure option for organisations using Active Directory, because password policies, expiration, and multi-factor authentication are all managed centrally by the IT department.

A SQL Server Login stores a username and password directly in SQL Server's own security system. This is necessary for applications that run on non-Windows platforms or need to connect across the internet without Windows domain trust.

Create a SQL Server login for a student application account:

CREATE LOGIN PortalApp WITH PASSWORD = 'SecurePassword#2024';

Users — Database-Level Access

A login grants access to the server. A database user is the login's identity within a specific database. The same login can be mapped to different users in different databases, or have no access to certain databases at all.

Switch to VoidX_Academy and create a user mapped to the login you just created:

USE VoidX_Academy;
CREATE USER PortalAppUser FOR LOGIN PortalApp;

At this point, PortalAppUser can connect to the server and access VoidX_Academy, but they cannot do anything inside it — no SELECT, no INSERT, nothing. Permissions must be explicitly granted.

Granting and Revoking Permissions

Use GRANT to give a user permission to perform specific operations on specific objects:

GRANT SELECT ON Student TO PortalAppUser;
GRANT SELECT ON Department TO PortalAppUser;
GRANT SELECT, INSERT ON Enrollment TO PortalAppUser;

The student portal application can now read students and departments, and can both read and insert enrollment records — but cannot UPDATE, DELETE, or access Wallet, Audit logs, or any other table. This is least-privilege in practice.

To remove a permission:

REVOKE INSERT ON Enrollment FROM PortalAppUser;

Use DENY to explicitly block a permission, overriding any role-based grants:

DENY SELECT ON Wallet TO PortalAppUser;

Even if PortalAppUser is later added to a role that has SELECT on Wallet, the DENY takes precedence. DENY is an absolute block.

Roles — Managing Groups of Permissions

Granting permissions individually to every user in a large organisation becomes unmanageable. Roles solve this by grouping permissions together. You assign users to a role, and they inherit all of the role's permissions instantly. Removing a user from a role revokes all those permissions immediately.

SQL Server has built-in fixed database roles. The most important ones to know are described here.

db_owner has full control of the database — equivalent to a database administrator. Assign this only to trusted DBAs.

db_datareader can run SELECT on every table in the database. No writes of any kind.

db_datawriter can INSERT, UPDATE, and DELETE on every table. No schema changes.

You can also create custom roles for more precise control:

CREATE ROLE AcademicsRole;
GRANT SELECT ON Student TO AcademicsRole;
GRANT SELECT ON Enrollment TO AcademicsRole;
GRANT SELECT ON Course TO AcademicsRole;
ALTER ROLE AcademicsRole ADD MEMBER PortalAppUser;

Now every future academic-facing account simply joins AcademicsRole rather than requiring three individual GRANT statements per user.

Backups — The Most Important Maintenance Task

All the security in the world cannot protect against a failed hard drive or accidental data deletion. Backups are your last line of defence. SQL Server supports three types of backup, which should be understood and used in combination.

A Full Backup copies the entire database — all data files, all objects, all rows. It is the foundation. Run a full backup of VoidX_Academy:

BACKUP DATABASE VoidX_Academy
TO DISK = 'C:\Backups\VoidX_Academy_Full.bak'
WITH FORMAT, COMPRESSION, STATS = 10;

FORMAT writes a new media header. COMPRESSION reduces the backup file size. STATS = 10 prints a progress message every 10% of completion in the Messages panel.

A Differential Backup copies only the data that has changed since the last full backup. It is faster than a full backup and much smaller. Run differential backups daily between weekly full backups:

BACKUP DATABASE VoidX_Academy
TO DISK = 'C:\Backups\VoidX_Academy_Diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

A Transaction Log Backup saves the transaction log, enabling point-in-time recovery. If you need to restore the database to exactly 10:47 AM — before a damaging operation at 10:50 AM — transaction log backups make this possible. Run log backups every hour in high-transaction systems:

BACKUP LOG VoidX_Academy
TO DISK = 'C:\Backups\VoidX_Academy_Log.trn';

Restoring from Backup

Knowing how to create backups is only half the skill — knowing how to restore from them when disaster strikes is equally important. Test your restores regularly. A backup you have never tested restoring may not be valid.

Restore from the full backup:

RESTORE DATABASE VoidX_Academy
FROM DISK = 'C:\Backups\VoidX_Academy_Full.bak'
WITH REPLACE, STATS = 10;

REPLACE is required if a database with the same name already exists on the server. Always disconnect all existing connections to the database before restoring.

Monitoring with Database Maintenance Checks

Two quick queries that belong in every database administrator's toolkit. The first checks how much space your database is using:

EXEC sp_spaceused;

The second checks for any errors recorded in the SQL Server error log in the last 24 hours:

EXEC xp_readerrorlog 0, 1, NULL, NULL, DATEADD(DAY, -1, GETDATE()), GETDATE();