SQL Server interview questions covering security, backup, recovery, administration & real-world scenarios.
Open a question for the full answer, code and interview guidance.
A login is a server-level security principal used to authenticate or identify access to a SQL Server instance. A database user is a database-level principal that provides access inside a specific database. A login can be mapped to users in multiple databases. Permissions should normally be assigned through database roles rather than individually wherever practical, following the principle of least privilege.
SQL Server supports Windows Authentication and SQL Server Authentication. Windows Authentication uses Windows or Active Directory identities and integrated security. SQL Server Authentication uses a SQL Server login name and password. An instance configured for Windows Authentication only does not accept SQL-authenticated logins. Mixed Mode allows both. In managed enterprise environments, integrated authentication is often preferred because identity, password policy, and access lifecycle can be centrally managed.
Least privilege means giving an account only the permissions required to perform its intended responsibilities, and no more. For example, a reporting application that only reads selected tables should not normally receive db_owner or sysadmin privileges. Least privilege reduces the potential impact of compromised credentials, programming errors, and accidental administrative actions.
Server roles group permissions at SQL Server instance scope, while database roles group permissions inside a database. Fixed server roles include powerful roles such as sysadmin, so membership must be tightly controlled. Database roles include built-in roles and custom roles. Custom roles are often preferable when an application requires a specific permission set rather than broad built-in privileges.
GRANT provides a permission. DENY explicitly prevents a permission and can override permissions inherited through many other paths. REVOKE removes a previously granted or denied permission entry, returning permission evaluation to other applicable grants, denies, and role memberships. Because SQL Server permission evaluation can involve multiple principals and scopes, security should be role-based and kept as simple as possible.
SQL injection occurs when untrusted input is incorporated into executable SQL syntax, allowing an attacker to alter the intended command. The primary defense is parameterization: use EF Core parameters, ADO.NET SqlParameter, Dapper parameters, or parameterized stored procedures. Avoid concatenating user input into SQL strings. Dynamic identifiers require strict allow-list validation and safe quoting because table and column names cannot be ordinary value parameters. Least-privilege database credentials provide an additional containment layer but do not replace parameterization.
A full database backup contains enough database data to restore the database to the point at which that backup completes, together with the log information needed to make the backup transactionally consistent. A full backup establishes a base for differential backups. A production recovery strategy usually combines full backups with other backup types according to recovery objectives rather than relying on full backups alone.
A differential backup contains data extents changed since the most recent full database backup that serves as its differential base. To restore using differentials, restore the appropriate full backup and then the latest required differential backup. Differential backups generally become larger as more data changes after the full backup, until a new full backup resets the differential base.
A transaction log backup captures transaction log records not previously backed up in the log backup chain. Under the FULL or BULK_LOGGED recovery model, log backups are central to point-in-time recovery and log management. Taking a full backup alone does not substitute for regular log backups when point-in-time recovery is required. A proper log backup chain must be maintained.
SQL Server provides SIMPLE, FULL, and BULK_LOGGED recovery models. SIMPLE automatically reuses log space after checkpoints when it is no longer required and does not support the normal transaction-log backup chain used for point-in-time recovery. FULL supports log backups and point-in-time recovery when the backup chain is maintained. BULK_LOGGED is similar to FULL but can reduce logging for certain bulk operations, with recovery implications during affected log backups. The model should be chosen from business recovery requirements, not merely database size.
RPO, or Recovery Point Objective, defines how much data loss the business can tolerate, expressed as a time interval. RTO, or Recovery Time Objective, defines how long the service can remain unavailable before it must be restored. For example, an RPO of 15 minutes may require sufficiently frequent log backups or replication technology, while a strict RTO may require tested automation, high availability, fast storage, or standby infrastructure. Backup and high-availability architecture should be designed from RPO and RTO requirements.
A restore sequence depends on the recovery model and available backup chain. A common point-in-time sequence restores the full backup WITH NORECOVERY, optionally restores the appropriate differential WITH NORECOVERY, restores required transaction log backups in sequence, and finally recovers the database. WITH NORECOVERY keeps the database ready to accept additional restore operations. WITH RECOVERY brings it online and ends the restore sequence. Restore procedures must be tested regularly; having backup files is not proof that recovery objectives can actually be met.
BACKUP ... WITH CHECKSUM can help detect certain I/O corruption during backup creation. RESTORE VERIFYONLY checks that SQL Server can read the backup set and that it appears structurally complete enough for restore processing. However, VERIFYONLY is not a substitute for performing an actual restore. The strongest operational test is to restore backups to a separate environment and run integrity and application-level validation.
DBCC CHECKDB checks the logical and physical integrity of database objects and allocation structures. It is a core SQL Server database-integrity tool. Production environments should run CHECKDB on a schedule appropriate to database size, criticality, and operational constraints. If corruption is found, the preferred response is normally to determine the cause and restore from known-good backups rather than immediately using repair options that may cause data loss.
SQL Server Agent is a service used to schedule and automate administrative and operational jobs. Typical tasks include backups, ETL processes, index/statistics maintenance, reporting, cleanup, and monitoring workflows. Jobs can contain multiple steps, schedules, retry behavior, and notifications. SQL Server Agent availability depends on the SQL Server edition and deployment model; cloud services may provide different scheduling mechanisms.
Query Store records query text, execution plans, and runtime performance statistics over time. It helps identify regressions, compare plan behavior, analyze workload history, and in supported scenarios influence plan selection. Query Store is especially useful when a query was fast previously but became slow after a plan change, deployment, statistics change, or data-growth event. It provides historical evidence that is often unavailable from the current plan cache alone.
Dynamic Management Views and Functions expose information about SQL Server state, workload, execution statistics, sessions, requests, indexes, memory, waits, and other engine activity. Examples include sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats, and sys.dm_db_index_usage_stats. DMV data is often transient and may reset after restart, failover, cache eviction, or other events, so it should be interpreted in context.
Start by confirming the slowdown with measurements rather than immediately changing code or adding indexes. Compare current behavior with the previous baseline if available. Inspect the actual execution plan, Query Store history, logical reads, CPU and elapsed time, blocking, waits, parameter values, cardinality estimates, statistics, recent deployments, data growth, plan changes, memory grants, spills, and index changes. The correct fix depends on the cause. A plan regression may require different action from blocking, stale statistics, parameter sensitivity, or a missing index.
First check the database recovery model and why log truncation is being delayed. In FULL recovery, missing transaction log backups are a common cause, but other causes include long-running transactions, availability/replication dependencies, active backups, and other log reuse waits. Inspect log_reuse_wait_desc, transaction activity, backup history, and disk capacity. Do not repeatedly shrink the log as a routine solution; shrinking without fixing the cause usually leads to repeated growth and fragmentation of the log file. After resolving the cause, size the log appropriately for normal workload and configure sensible autogrowth.
Daily checks should be driven by service-level requirements and automation, but a practical production checklist includes failed jobs, backup success and recoverability, database availability, disk and log space, blocking/deadlocks, critical errors, abnormal waits, performance regressions, integrity-check status, replication or availability health where applicable, and security or configuration alerts. A mature environment automates collection and alerting instead of depending entirely on manual checks. Trends are often more useful than isolated numbers: database growth, backup duration, query latency, CPU, I/O, waits, and job duration should be monitored over time. For application developers, the same operational mindset is valuable: production database performance, recovery, security, and observability are part of application reliability, not solely DBA responsibilities.