Security and EncryptionLesson 1
Authentication and Authorisation
14 minutes
Database security starts with two questions: who are you (authentication), and what are you allowed to do (authorisation). Get either of these wrong and the strongest encryption in the world will not save you. This lesson covers the authentication options for SQL Server, the role and permission model and the principles that turn a flexible system into a secure one.
Why this matters
Most database breaches do not come from a sophisticated attacker cracking the encryption. They come from an over-permissioned application account, a forgotten test login or a single missing GRANT/REVOKE check. The boring discipline of getting permissions right is the most consequential security work in any database.
Authentication: who are you
SQL Server supports several authentication modes. The choice depends on the environment and the threat model.
SQL authentication
1
How it works
Username and password stored inside SQL Server. The application sends them on connect. SQL Server validates against its own login table.
2
When to use
Cloud-hosted SQL Server where Active Directory does not reach. Application accounts that need to authenticate without a Windows context. Quick local development.
3
The risks
Passwords in connection strings. If the password is committed to source control, exposed in an environment-variable dump or stolen via memory-scraping malware, the account is compromised. Rotation is hard because every consumer needs to update.
Windows / Active Directory authentication
1
How it works
The user is already authenticated to Windows / AD; SQL Server trusts the Windows token. No password sent at connection time.
2
When to use
Corporate environments with AD. On-prem SQL Server. Domain-joined application servers running as service accounts.
3
The advantages
No passwords in connection strings. Account rotation happens at the AD level; databases inherit. Single sign-on for users. Group memberships in AD become permission boundaries in SQL Server.
Azure AD / Entra ID authentication
1
How it works
Cloud identity (Entra ID, formerly Azure AD). Tokens issued by Microsoft Identity Platform; SQL Server validates against the tenant.
2
When to use
Azure SQL Database, Azure SQL Managed Instance, modern cloud SQL Server. The recommended default for new deployments.
3
The advantages
MFA support. Conditional access policies. Identity governance and audit at the tenant level. No long-lived passwords for application service principals (use managed identities instead).
IAM (AWS RDS)
1
How it works
AWS IAM issues short-lived auth tokens that the application exchanges for a database connection. Token valid for 15 minutes.
2
When to use
AWS RDS deployments. Application running on AWS infrastructure with an IAM role.
3
The advantages
No database password to manage. Identity governance and rotation happen at the AWS IAM level. Audit trail in CloudTrail.
Certificate authentication
1
How it works
Client presents a TLS certificate signed by a trusted CA. SQL Server validates the certificate against its trust store.
2
When to use
Machine-to-machine connections in zero-trust networks. Kubernetes pods with cert-based identity. Some highly-regulated environments.
3
The advantages
No passwords; identity is bound to the certificate. Hard to phish. Granular cert lifecycle (rotation, revocation).
A practical recommendation
Prefer identity-based authentication over passwords
If you can use Windows / Entra ID / IAM / certificates, do. Reserve SQL authentication for cases where identity-based options are not available (e.g., a Linux app server connecting to an on-prem SQL Server with no AD trust). For passwords that remain, use a secrets manager (Azure Key Vault, AWS Secrets Manager, HashiCorp Vault) and rotate regularly. Never commit passwords to source control.
Authorisation: what are you allowed to do
Once SQL Server knows who you are, the permissions system decides what you can do. The model has two layers.
Logins vs users
1
Login
Server-level. Authenticates against SQL Server. The thing you connect with.
2
User
Database-level. A mapping from a login to permissions within a specific database. One login can have multiple users (one per database) or no user in some databases.
-- Create a login at the server level
CREATE LOGIN AppUser WITH PASSWORD = 'Strong!Pa55w0rd';
-- Map it to a user in a specific database
USE BookshopDB;
CREATE USER AppUser FOR LOGIN AppUser;The separation exists because the same login may have different permissions in different databases. AppUser may be a read-write user in BookshopDB and read-only in AnalyticsDB.
Roles
A role is a named collection of permissions. Users are members of roles; the role's permissions apply to all members.
1
Database roles (built-in)
db_owner (full control of one database), db_datareader (read all tables), db_datawriter (write all tables), db_ddladmin (DDL). Use sparingly; usually too coarse.2
Server roles (built-in)
sysadmin (full server control), dbcreator, securityadmin. Treat membership in sysadmin like root: rare, audited, justified.3
Custom roles
Create your own roles aligned to actual application needs:
ReportingReader, OrderProcessor, CustomerSupport. Each has specific permissions. Users get the roles that match their job.-- Create a custom role
CREATE ROLE OrderProcessor;
-- Grant specific permissions to it
GRANT SELECT, INSERT, UPDATE ON Customers TO OrderProcessor;
GRANT SELECT, INSERT, UPDATE ON Orders TO OrderProcessor;
GRANT EXECUTE ON dbo.sp_PlaceOrder TO OrderProcessor;
GRANT EXECUTE ON dbo.sp_GetCustomerOrders TO OrderProcessor;
-- No DELETE: this role cannot remove rows
-- No access to AuditLog: orthogonal concern
-- Add a user to the role
ALTER ROLE OrderProcessor ADD MEMBER AppUser;GRANT, REVOKE and DENY
Three permission verbs. The semantics matter.
| Verb | Effect | When to use |
|---|---|---|
| GRANT | Allow the permission | Standard way to enable access |
| REVOKE | Remove a previously granted permission (returning to unset) | Undo a GRANT; permission falls back to whatever role memberships provide |
| DENY | Explicitly forbid the permission, overriding any GRANT from any role | Block a specific user even if they are a member of a role that has the permission. The strongest 'no'. |
DENY overrides GRANT
A user who is a member of a role that grants SELECT but also has an explicit DENY SELECT on the same object cannot read. DENY is the override. Used sparingly: most teams use GRANT-only and rely on role membership to control access. DENY is for the exceptional case where you must block a specific user from something their role would otherwise allow.
The principle of least privilege
The most important authorisation principle: every account gets the minimum permissions needed for its job, and no more.
Application accounts should not be sysadminDefault mistake: the application account is given sysadmin because 'it just works'. If the application is compromised (SQL injection, credential leak), the attacker has full server control. Application accounts should have permissions for the specific tables/procedures they need.
Read-only accounts for reportingAnything that only reads should have a SELECT-only account. Cannot accidentally modify; cannot be exploited to write.
EXECUTE on procedures, no direct SELECTWhen stored procedures are the contract, grant EXECUTE on the procedures and DENY direct access to the underlying tables. The procedure is the only way to reach the data; rules enforced consistently.
Separate accounts per serviceIf three different applications hit the same database, each gets its own login and user. Permissions tailored to what each service does. One compromise does not give access to the others' tables.
A typical permission model
For a SaaS product on SQL Server, a reasonable starting point:
1
WebAppLogin
Application login that the web app connects with. Database user mapped to a custom role (AppUser) with EXECUTE on a curated set of stored procedures and SELECT on a few views. No direct table access.
2
ReportingLogin
Read-only account for the reporting / BI tool. Member of db_datareader (or a more curated role granting SELECT on specific tables). No DDL, no DML.
3
DBALogin
For database administrators. Member of db_owner on the specific database. Used interactively, not by applications. Audited.
4
BackupLogin
The login the backup job uses. Permissions: BACKUP DATABASE, BACKUP LOG. Nothing else.
5
No-one is sysadmin in production
Sysadmin is for emergencies. Day-to-day administration uses scoped roles. If sysadmin is needed regularly, the permissions of the day-to-day role are wrong.
Key Takeaways
- Authentication options: SQL, Windows/AD, Entra ID, IAM, certificate. Prefer identity-based over passwords whenever available.
- Logins are server-level; users are database-level. One login can be different users in different databases.
- Use custom roles aligned to actual job functions. Built-in roles like db_owner are usually too coarse.
- GRANT to enable, REVOKE to undo, DENY to override. DENY beats GRANT from any role.
- Principle of least privilege. Every account gets the minimum permissions needed; never sysadmin for applications.