Audit Server Configuration Drift with sys.configurations

calendar Aug 24, 2026 / · 8 min read · sql server sys.configurations sp_configure reconfigure configuration drift advanced options xp_cmdshell security monitoring scripts  ·
Share on: linkedin copy

Running sp_configure and getting back a success message is not the same thing as a setting actually taking effect. Between the moment a value is changed and the moment SQL Server is actually running it sits a gap — one a busy DBA can close with a single RECONFIGURE, or forget entirely until a restart months later flips a setting no one meant to touch yet. sys.configurations is where that gap is visible: its value and value_in_use columns don't have to match, and when they don't, this script says exactly why not and what to do about it.

Purpose and Overview

Which configuration changes are actually running on an instance right now, and which are still waiting on a RECONFIGURE — or a restart that hasn't happened yet? sys.configurations answers both in a single query, because it returns one row per server-wide configuration option and stores the configured value and the running value as two separate columns instead of collapsing them into one: value is the setting as last configured, value_in_use is the setting actually in effect.

Beyond those two, the view carries is_dynamic — whether a RECONFIGURE alone is enough to apply the change, or whether the Database Engine needs a restart first — and is_advanced, which flags options that Management Studio and sp_configure hide from a plain listing until show advanced options is turned on. minimum, maximum, and description round out the row. Querying the view itself needs nothing more than membership in the public role on SQL Server 2019 and earlier; SQL Server 2022 and later narrow that to the VIEW SERVER PERFORMANCE STATE permission instead.

Two rows are documented exceptions to a clean value/value_in_use match, and worth knowing before they're mistaken for drift: max server memory (MB) defaults to a configured 0 but reports a value_in_use of 2147483647, and min server memory (MB) defaults to 0 but can report 8 or 16 depending on platform. Every other option showing a mismatch is a real candidate for investigation, which is what the script below is built to separate.

Code Breakdown

The script runs two passes over the same catalog view: a drift-detail query that flags every option whose configured value hasn't taken effect yet, and a companion query that lists every advanced option currently switched on, whether or not it made anyone's checklist.

 1SELECT
 2    c.configuration_id,
 3    c.name,
 4    c.value                                                        AS configured_value,
 5    c.value_in_use                                                 AS running_value,
 6    c.is_dynamic,
 7    c.is_advanced,
 8    CASE
 9        WHEN c.is_dynamic = 1 THEN 'RECONFIGURE not yet run'
10        ELSE 'Restart required'
11    END                                                             AS drift_reason,
12    CASE
13        WHEN c.name IN (N'max server memory (MB)', N'min server memory (MB)')
14            THEN 'Expected — documented exception, not real drift'
15        ELSE 'Investigate'
16    END                                                             AS drift_flag,
17    c.description
18FROM sys.configurations AS c
19WHERE c.value <> c.value_in_use
20ORDER BY c.is_dynamic ASC, c.name ASC;

Filtering on value <> value_in_use, and the two documented exceptions

The WHERE c.value <> c.value_in_use predicate is a direct extension of the query Microsoft's own reference for sys.configurations publishes to answer the question "have any configured values not been installed" — select * from sys.configurations where value != value_in_use. The script adds a drift_flag column specifically to keep the two known-benign rows (the memory options above) from reading as unexplained problems on every single run; anything else appearing in this result set genuinely hasn't finished landing.

Routing the fix with is_dynamic

is_dynamic = 1 means the new value takes effect as soon as RECONFIGURE runs — in most cases immediately, though the documentation notes the engine may not evaluate it until the normal course of execution. is_dynamic = 0 means the changed value sits in value doing nothing until the instance is stopped and restarted, no matter how many times RECONFIGURE is executed against it. The drift_reason column in the query above translates that flag directly into the next action: run RECONFIGURE, or schedule a restart. For a non-dynamic option there's no way to tell from the DMV alone whether RECONFIGURE was ever run as the first step — Microsoft's own guidance is to run it anyway before a planned restart, just to be sure the pending change is queued correctly.

 1SELECT
 2    c.name,
 3    c.value_in_use,
 4    c.is_advanced,
 5    c.is_dynamic,
 6    c.description
 7FROM sys.configurations AS c
 8WHERE c.is_advanced = 1
 9    AND c.value_in_use = 1
10ORDER BY c.name;

Surfacing every enabled advanced option, not just the well-known ones

is_advanced = 1 is the same flag that keeps an option out of a plain sp_configure listing until show advanced options is set to 1 — a deliberate speed bump, since Microsoft's own documentation adds a caution that turning it on applies to every connection on the instance for as long as it's set. Filtering to is_advanced = 1 AND value_in_use = 1 sidesteps that gate entirely and lists every advanced option currently switched on, regardless of whether anyone remembers enabling it. Three that regularly turn up here carry real security weight: xp_cmdshell, disabled by default and capable of spawning a Windows command shell from inside a session; Ad Hoc Distributed Queries, also off by default, which opens OPENROWSET and OPENDATASOURCE access to any authenticated login; and clr enabled, off by default, which permits user assemblies to run inside the engine.

Why this script never calls RECONFIGURE itself

Reading sys.configurations needs nothing beyond public role membership (or VIEW SERVER PERFORMANCE STATE on SQL Server 2022+), but changing anything requires the ALTER SETTINGS server-level permission, held implicitly only by the sysadmin and serveradmin fixed roles. Even with that permission in hand, RECONFIGURE is not allowed inside an explicit or implicit transaction, and when several options are reconfigured together, a single failure means none of them take effect — an all-or-nothing behavior worth knowing before wrapping a batch of sp_configure calls in one script. RECONFIGURE WITH OVERRIDE removes the range and cross-option checks — the kind that normally blocks a recovery interval over 60 minutes or an affinity mask that overlaps affinity I/O mask — which is exactly why this script only reads and reports, and leaves the decision to reconfigure as a separate, deliberate step.

Key Benefits and Use Cases

  • Confirms a change actually landed — a sp_configure success message only means the request was accepted, not that value_in_use moved.
  • Separates the two fixes — is_dynamic routes each drifted option to either "run RECONFIGURE" or "schedule a restart," instead of guessing.
  • Surfaces advanced options quietly left on — is_advanced = 1 AND value_in_use = 1 catches settings a vendor installer or a one-off script may have flipped without anyone noticing.
  • Filters the two documented false positives — the memory-option exceptions are labeled instead of flagged as unexplained drift on every run.
  • Runs with minimal permissions — public role membership (or VIEW SERVER PERFORMANCE STATE on 2022+) is enough to read; no elevated access needed just to look.
  • No setup required — a single catalog view, no trace flags, no Extended Events session.

Performance Considerations

  • Permission floor changed in SQL Server 2022: public role membership covers earlier versions; 2022 and later require VIEW SERVER PERFORMANCE STATE instead.
  • Reading is free, changing isn't: this script only queries; applying a fix needs ALTER SETTINGS, held implicitly by sysadmin and serveradmin.
  • RECONFIGURE is all-or-nothing across a batch: if several options are reconfigured together and one fails validation, none of them take effect.
  • RECONFIGURE refuses to run inside a transaction: explicit or implicit, so it has to stand on its own in a script or job step.
  • WITH OVERRIDE removes the safety rails: it skips the valid-range and cross-option checks entirely, which is why Microsoft's own documentation calls for using it cautiously.

Practical Tips

  • Re-run the drift query immediately after any RECONFIGURE — a clean result outside the two memory-option exceptions is the actual confirmation the change is live, not the success message from sp_configure.
  • Schedule the advanced-options query as a recurring job and diff the output against the previous run, so a vendor installer that silently enabled xp_cmdshell doesn't go unnoticed for months.
  • Treat any non-dynamic option showing drift as a maintenance-window item — it will not resolve itself no matter how many times RECONFIGURE runs without a restart behind it.
  • Turn show advanced options back to 0 as soon as a manual review is done; it changes visibility for every connection on the instance while it's set to 1.
  • Run plain RECONFIGURE before reaching for WITH OVERRIDE — the validation it performs against documented ranges is often the more useful signal than forcing the change through.

Conclusion

sys.configurations, filtered on value <> value_in_use and cross-checked against is_dynamic and is_advanced, turns "did that configuration change actually take effect" from a guess into a specific, actionable list — restart this, RECONFIGURE that, and take a second look at this advanced option someone left on. Run on a schedule, it catches both categories of drift a DBA is most likely to miss: the change that never finished landing, and the security-relevant option nobody remembers enabling.

References

Posts in this series