SQL Server Q&A Topic 10: Security

SQL Server Interview Questions & Answers

Topic 10: Security

Questions 91–96 | Beginner to Intermediate

This section covers fundamental SQL Server security concepts including logins, database users, server roles, database roles, Transparent Data Encryption (TDE), Always Encrypted and the principle of least privilege.

SQL Server security should be designed in layers. Authentication controls who can connect, authorization controls what they can access, and encryption helps protect sensitive data.

91. Login vs database user?

Answer:
A login is generally a server-level security principal used to authenticate access to a SQL Server instance.

A database user is a database-level security principal that provides access inside a particular database.

A simple way to remember this is:

Login
Authentication at SQL Server instance level

↓

Database User
Authorization inside a database

Example:

CREATE LOGIN AppLogin
WITH PASSWORD = 'Use-A-Strong-Password-Here';

USE SalesDB;
GO

CREATE USER AppUser
FOR LOGIN AppLogin;

The login allows authentication to the SQL Server instance, while the database user allows permissions to be assigned inside SalesDB.

A server-level login can be mapped to different database users in different databases.

Interview Tip:
Think of it as: Login = instance access and User = database access.

92. What are fixed server roles?

Answer:
Fixed server roles are predefined SQL Server roles that provide server-level permissions and administrative capabilities.

Traditional fixed server roles include:

• sysadmin
• serveradmin
• securityadmin
• processadmin
• setupadmin
• bulkadmin
• diskadmin
• dbcreator
• public

Modern SQL Server versions also provide additional fixed server roles designed around more granular server-level permissions.

Membership can be reviewed using:

SELECT
    r.name AS ServerRole,
    m.name AS MemberName
FROM sys.server_role_members AS rm
INNER JOIN sys.server_principals AS r
    ON rm.role_principal_id = r.principal_id
INNER JOIN sys.server_principals AS m
    ON rm.member_principal_id = m.principal_id
ORDER BY
    r.name,
    m.name;

Security Tip:
Avoid granting sysadmin simply because it is convenient. Members of sysadmin effectively have unrestricted control over the SQL Server instance.

93. What are database roles?

Answer:
Database roles are database-level security principals used to group users and manage permissions more efficiently.

Instead of granting the same permissions individually to many users, permissions can be granted to a role and users can then be added to that role.

Common fixed database roles include:

• db_owner
• db_securityadmin
• db_accessadmin
• db_backupoperator
• db_ddladmin
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

Custom database roles are often preferable because they allow permissions to match actual application or business requirements.

Example:

USE SalesDB;
GO

CREATE ROLE SalesReporting;

GRANT SELECT
ON SCHEMA::Reporting
TO SalesReporting;

ALTER ROLE SalesReporting
ADD MEMBER ReportUser;

DBA Tip:
Role-based security is generally easier to administer and audit than assigning many individual permissions directly to users.

94. What is Transparent Data Encryption?

Answer:
Transparent Data Encryption, commonly called TDE, encrypts SQL Server database data at rest.

TDE encrypts database data files and transaction log files using a database encryption key.

Backups of a TDE-enabled database are also encrypted.

A simplified encryption hierarchy is:

Service Master Key
   ↓
Database Master Key in master
   ↓
Certificate
   ↓
Database Encryption Key
   ↓
Database Data and Log Files

TDE is transparent to applications because applications generally do not need to change their queries to access encrypted databases.

However, TDE does not prevent an authorized database user from reading data through normal SQL queries.

Critical DBA Tip:
Back up the certificate and associated private key used to protect the TDE database encryption key. Losing the required encryption material can prevent a database backup from being restored on another SQL Server instance.

95. TDE vs Always Encrypted?

Answer:
TDE and Always Encrypted both provide encryption, but they protect data at different layers and solve different security requirements.

Area	TDE	Always Encrypted
Primary Protection	Data at rest	Selected sensitive column values
Encryption Scope	Database files, log files and backups	Configured columns
Application Awareness	Generally transparent	Requires an Always Encrypted-enabled client driver
DBA Can Normally Read Plaintext Through SQL	Yes, with sufficient database permissions	Not without access to the required encryption keys

In simple terms:

TDE
Protects database files, log files and backups at rest.

Always Encrypted
Protects selected sensitive values so the Database Engine can operate without necessarily having access to plaintext values.

Common Interview Trap:
TDE does not prevent a sufficiently privileged SQL Server user from querying plaintext data through the database engine. Its primary purpose is protection of data at rest.

96. What is the principle of least privilege?

Answer:
The principle of least privilege means that a user, application or service should receive only the minimum permissions required to perform its legitimate function.

For example, if an application only needs to execute three stored procedures, granting it db_owner would normally provide far more privileges than required.

Instead, permissions could be granted only on the required procedures.

GRANT EXECUTE
ON dbo.usp_CreateOrder
TO ApplicationRole;

GRANT EXECUTE
ON dbo.usp_UpdateOrder
TO ApplicationRole;

GRANT EXECUTE
ON dbo.usp_GetOrder
TO ApplicationRole;

A broader role-based design could also grant permissions at schema level where appropriate.

GRANT EXECUTE
ON SCHEMA::Sales
TO ApplicationRole;

Least privilege helps reduce:

• Unauthorized data access
• Accidental modifications
• Security exposure from compromised accounts
• Unnecessary administrative privileges
• The potential impact of application vulnerabilities

Interview Tip:
Instead of giving an application sysadmin, db_owner or broad read/write privileges, determine exactly which databases, schemas, tables or procedures it requires and grant only those permissions.

Useful DBA Script: Check SQL Server Logins

SELECT
    name AS LoginName,
    type_desc AS LoginType,
    is_disabled AS IsDisabled,
    create_date AS CreatedDate,
    modify_date AS ModifiedDate
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G', 'E', 'X')
ORDER BY name;

Useful DBA Script: Check Database Role Membership

SELECT
    role_principal.name AS DatabaseRole,
    member_principal.name AS MemberName
FROM sys.database_role_members AS drm
INNER JOIN sys.database_principals AS role_principal
    ON drm.role_principal_id = role_principal.principal_id
INNER JOIN sys.database_principals AS member_principal
    ON drm.member_principal_id = member_principal.principal_id
ORDER BY
    role_principal.name,
    member_principal.name;

Useful DBA Script: Check TDE Status

SELECT
    d.name AS DatabaseName,
    d.is_encrypted AS IsEncrypted,
    dek.encryption_state,
    CASE dek.encryption_state
        WHEN 0 THEN 'No Database Encryption Key'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption In Progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key Change In Progress'
        WHEN 5 THEN 'Decryption In Progress'
        WHEN 6 THEN 'Protection Change In Progress'
        ELSE 'Unknown'
    END AS EncryptionState,
    dek.encryptor_type
FROM sys.databases AS d
LEFT JOIN sys.dm_database_encryption_keys AS dek
    ON d.database_id = dek.database_id
ORDER BY d.name;

Simplified SQL Server Security Model

Identity
   ↓
Authentication / Login
   ↓
Database User
   ↓
Database or Custom Role
   ↓
Permissions
   ↓
Schema / Object / Procedure Access
   ↓
Audit and Review
   ↓
Periodic Access Recertification

Interview Quick Recap

• A login normally provides authentication at SQL Server instance level.
• A database user represents access inside a database.
• Fixed server roles provide predefined server-level permissions.
• Avoid granting sysadmin unless unrestricted administrative access is genuinely required.
• Database roles simplify permission management.
• Custom roles can provide more precise permissions than broad fixed roles.
• TDE protects SQL Server database files, transaction log files and database backups at rest.
• TDE does not protect data from a database user who already has sufficient permission to query it.
• Always Encrypted is designed to protect selected sensitive column values using client-side encryption capabilities.
• TDE and Always Encrypted solve different security requirements and can be complementary.
• Least privilege means granting only the permissions required to perform the intended task.

← Previous Topic
# Topic 9: Isolation & Concurrency

Next Topic →

# Topic 11: HA/DR & Administration

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning