SQL Server environments that are patched, firewalled, and configured to best practice can still carry hidden risk. Global temporary objects, Dynamic Management Views (DMVs), and audit specifications are all legitimate, widely-used SQL Server capabilities. Yet, each can quietly become part of an attack chain when granted too broadly, trusted inside privileged workflows, or only validated against the obvious case.
This article, part of Fabiano Amorim’s complete guide to SQL Server security, explains and demonstrates where these three features commonly go wrong. Learn the the exact signs and queries to check for exposure – and the practical fixes that turn a theoretical risk into a closed gap.
Over the course of this series, I’ve challenged the common assumption that a SQL Server environment is safe simply because it’s patched, firewalled, and configured according to familiar defaults.
I’ve proven that this is not quite true, because real security is not a checkbox. It requires a verification mindset, especially in areas where SQL Server intentionally provides powerful operational features for DBAs, developers, monitoring tools, and application frameworks.
In this guide, I’ll focus on three features that are easy to underestimate because they look like normal SQL Server behavior. They are global temporary objects, Dynamic Management Views (DMVs), and audit coverage – each a useful feature in their own right.
The other thing they all have in common? They can each become part of an attack chain if granted too broadly, used inside privileged workflows, or trusted without validation.
My goal here is not to provide a deep exploit walkthrough – that’s for my other series. Here, I want to help you, a DBA, answer the practical questions. Where am I exposed? What should I look for (and what should I monitor?) Then, what should I change when I find a risky pattern?
Why these SQL Server risks matter
The risks in this article don’t necessarily start with an obviously dangerous permission. They may, in fact, look quite reasonable in isolation.
You might not bat an eyelid at, for example, a developer using a global temporary table, a vendor requesting VIEW SERVER STATE, or an auditor enabling a sensitive-data audit group. The danger comes when these choices are combined with elevated SQL Agent jobs, predictable object names, dynamic SQL, broad monitoring visibility, or narrow audit assumptions.
Often times, an attacker with a low-privileged foothold only needs to influence something that a trusted process will later consume, observe enough metadata to understand where the trust boundaries are, or choose an access path that was never included in the audit test plan.
All of this is exactly why SQL Server DBAs should take the security of these features seriously, and treat them as operational trust boundaries. In this guide, I’ll help you to achieve this.
SQL Server global temporary tables and procedures
SQL Server temporary objects are stored in tempdb. Local temporary tables, prefixed with a single #, are scoped to the creating session. Global temporary tables and global temporary procedures prefixed with ##, meanwhile, are different – they’re visible outside the session that created them.
This distinction is where many design mistakes begin, as temporary does not mean ‘private’ – and global means ‘shared’.
The risk becomes serious when a privileged process relies on a predictable global temporary object. Examples include SQL Server Agent jobs running as sa, maintenance procedures executing as dbo, vendor routines that use global scratch tables, or administrative scripts that pass state between job steps through ## objects.
If lower-privileged users can predict the name, observe the lifecycle, pre-create a competing object, or influence the rows consumed by the privileged process, the temporary object becomes a trust-boundary problem. It’s that simple.
Global temporary procedures deserve special attention. A global temporary table may allow data poisoning, and a global temporary procedure is executable code. If privileged code executes a ## procedure by name, the important security question is: who controlled the code body that name resolved to at runtime?
An example of this in action
Consider a custom SQL Agent job that performs nightly user provisioning. It first creates a global table called ##UserProvisioningQueue and loads rows from an external feed. Then, it reads that table and builds dynamic SQL to create logins or assign roles. The job owner is sa because the workflow performs server-level administration.
A compromised low-privileged account that can discover this pattern may try to create the same global object before the job starts – or may wait for the object to appear and attempt to insert crafted rows.
If the privileged job doesn’t validate the object origin, schema, and content, it may process attacker-controlled input under the sa context.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/* Find SQL Agent job steps that reference global temporary objects */ SELECT j.name AS job_name, SUSER_SNAME(j.owner_sid) AS job_owner, s.step_id, s.step_name, s.subsystem, s.database_name, s.command FROM msdb.dbo.sysjobs AS j JOIN msdb.dbo.sysjobsteps AS s ON j.job_id = s.job_id WHERE s.command LIKE '%##%' ORDER BY j.name, s.step_id; /* Find programmable objects that reference ## objects */ SELECT DB_NAME() AS database_name, SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.type_desc FROM sys.sql_modules AS m JOIN sys.objects AS o ON m.object_id = o.object_id WHERE m.definition LIKE '%##%' ORDER BY schema_name, object_name; |
What are the signs a DBA should look for?
- SQL Agent job steps, stored procedures, ETL routines, or vendor scripts that create or consume objects with names beginning with ##.
- Privileged jobs that pass security-sensitive state between job steps through global temporary tables instead of local temp tables or permanent secured staging tables.
- Global temporary procedures in
tempdb– especially if their names are predictable or if they’re executed by elevated jobs. - Dynamic SQL built from rows stored in temporary tables, particularly when the job owner is
saor another sysadmin-equivalent principal. - Repeated errors such as ‘There is already an object named…’ around the same time privileged jobs start, which may indicate name collisions or probing.
What to monitor – and how
Code and job-step review
Schedule a recurring check against msdb.dbo.sysjobsteps and sys.sql_modules for references to ##. Treat any result in a privileged workflow as something that needs design review, not just code cleanup.
Current tempdb object inventory
Periodically sample tempdb for global temporary objects and correlate creation windows with privileged jobs. This is especially useful during testing and incident response.
Extended Events
Capture object creation and DDL (data declaration language) activity in tempdb, together with session_id, username, client_app_name, client_hostname, and sql_text. Validate the exact event fields in your SQL Server version before promoting the session to production.
Future-proof database monitoring with Redgate Monitor
SQL Audit
Where appropriate, layer schema/object change auditing with job definition monitoring. Audit alone is not a substitute for removing the risky design, but it helps detect unexpected object creation and modification.
Job history
Review job failures and retries around steps that create global temporary objects. A sudden collision or object-exists error can be a signal that another session created the expected object first.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
/* Current global temporary objects in tempdb */ SELECT o.name, o.type_desc, o.create_date, o.modify_date FROM tempdb.sys.objects AS o WHERE o.name LIKE '##%' ORDER BY o.create_date DESC; /* Job failures near global-temp workflows */ SELECT TOP (100) j.name AS job_name, h.step_id, h.step_name, h.run_date, h.run_time, h.run_status, h.message FROM msdb.dbo.sysjobhistory AS h JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id WHERE h.run_status <> 1 AND EXISTS ( SELECT 1 FROM msdb.dbo.sysjobsteps AS s WHERE s.job_id = j.job_id AND s.command LIKE '%##%' ) ORDER BY h.instance_id DESC; /* XE starting point: detect batches/RPCs that reference global temp objects */ CREATE EVENT SESSION [Watch_Global_Temp_Object_References] ON SERVER ADD EVENT sqlserver.sql_batch_completed ( ACTION ( sqlserver.session_id, sqlserver.server_principal_name, sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name ) WHERE (sqlserver.like_i_sql_unicode_string([batch_text], N'%##%')) ), ADD EVENT sqlserver.rpc_completed ( ACTION ( sqlserver.session_id, sqlserver.server_principal_name, sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name ) WHERE (sqlserver.like_i_sql_unicode_string([statement], N'%##%')) ) ADD TARGET package0.event_file ( SET filename = N'C:\SQLAudit\Watch_Global_Temp_Object_References.xel', max_file_size = 100, max_rollover_files = 5 ); GO ALTER EVENT SESSION [Watch_Global_Temp_Object_References] ON SERVER STATE = START; GO |
Practical DBA interpretation
Finding ## in code is not automatically a vulnerability. The risk depends on who creates it, who can see it (and write to it), the predictability of its name, whether privileged code trusts it, and whether it feeds dynamic SQL or administrative decisions. The review should focus on the trust boundary rather than just the prefix.
Recommended fixes or mitigations
- Remove global temporary objects from privileged workflows, using local temporary tables where possible. Local # tables are session-scoped and avoid the cross-session trust problem.
- Use secured permanent staging tables when state must cross sessions. If multiple job steps or sessions must exchange data, use a dedicated schema with strict permissions, ownership, validation constraints, and clear cleanup rules.
- Avoid global temporary procedures; do not let privileged code execute ## procedures by predictable name! Package helper logic in signed stored procedures or controlled permanent modules instead.
- Use unpredictable names only as a fallback. A GUID (globally unique identifier) suffix can reduce object hijacking risk, but doesn’t fix the core design if lower-privileged sessions can still influence the object.
- Validate before use. If a legacy workflow can’t be changed immediately, validate the
object_id,schema, expected columns, expected data types, row source, and row values before privileged code consumes the object. - Review dynamic SQL. Never concatenate administrative commands directly from temporary-table rows. Use parameterized calls,
QUOTENAMEfor identifiers, strict allowlists/whitelists, and explicit validation.
Information disclosure through DMVs and metadata visibility in SQL Server
DMVs, catalog views, traces, Query Store, Extended Events, and job history are essential for troubleshooting. They’re also excellent reconnaissance tools.
While a login with broad observability rights may not have SELECT permission on sensitive tables, it may still be able to learn database names, job names, linked server names, query text, parameter values, execution patterns, privileged account names, and application architecture. This matters because attackers rarely need full data access at the start of a campaign.
Instead, they just need a map telling them which accounts are trusted, which jobs run as sa, which databases look sensitive, which procedures use EXECUTE AS OWNER, which linked servers exist, and which monitoring rules are watching them. Broad metadata visibility can provide that map.
The risk increased in complexity with newer SQL Server versions, with some DMV access now split across more granular permissions (such as VIEW SERVER PERFORMANCE STATE in SQL Server 2022 onwards). It’s useful, yes, but doesn’t remove the need for review.
Simply put: a monitoring account should receive the smallest permission set necessary for the exact telemetry it needs.
An example of this in practice
A third-party monitoring account is granted VIEW SERVER STATE because the vendor dashboard needs to display active sessions and wait statistics. Later, the account is compromised through the application server that hosts the monitoring tool. The attacker can’t query the Finance database directly, but they can observe currently running requests and retrieve query text through DMVs.
From that visibility, the attacker learns the name of a privileged password-reset procedure, sees which job checks for unauthorized sysadmins, discovers which linked server is used for financial reporting, and identifies the Windows group that the monitoring script treats as approved.
So, even if no data rows are stolen at this stage, the attacker has learned how the environment defends itself – and where to aim the next step.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
/* Review broad server-level observability permissions */ SELECT pr.name AS principal_name, pr.type_desc, pe.state_desc, pe.permission_name FROM sys.server_permissions AS pe JOIN sys.server_principals AS pr ON pe.grantee_principal_id = pr.principal_id WHERE pe.permission_name IN ( 'VIEW SERVER STATE', 'VIEW SERVER PERFORMANCE STATE', 'VIEW ANY DEFINITION', 'ALTER ANY EVENT SESSION', 'ALTER TRACE', 'CONTROL SERVER' ) ORDER BY pr.name, pe.permission_name; /* Review fixed server roles that indirectly imply broad visibility or control */ SELECT role_name = roles.name, member_name = members.name, members.type_desc FROM sys.server_role_members AS srm JOIN sys.server_principals AS roles ON srm.role_principal_id = roles.principal_id JOIN sys.server_principals AS members ON srm.member_principal_id = members.principal_id WHERE roles.name IN ('sysadmin', 'securityadmin', 'serveradmin') ORDER BY roles.name, members.name; |
What are the signs a DBA should look for?
- Application service accounts, reporting users, and vendor tools. Additionally, any AD (Active Directory) groups granted
VIEW SERVER STATE,VIEW SERVER PERFORMANCE STATE,VIEW ANY DEFINITION,ALTER TRACE, orALTER ANY EVENT SESSION. - Support accounts that can read job definitions, job history, query text, execution plans, Query Store data, or Extended Events output without a clear operational need.
- Monitoring jobs or DBA scripts that include sensitive server names, approved-admin lists, credentials, tokens, or detection logic in plain T-SQL text.
- Unusual spikes in conversion errors, divide-by-zero errors, or syntax errors against system views, which may indicate error-based metadata probing.
- Repeated calls to
sys.dm_exec_sql_text,sys.dm_exec_query_plan,sys.dm_exec_requests,fn_trace_gettable, or Extended Events file readers from accounts that don’t usually perform troubleshooting.
What to monitor – and how
- Permission baseline. Store a baseline of server-level permissions and fixed server-role membership. Alert on new grants of broad observability permissions, especially to service accounts and domain groups.
- Error probing. Use Extended Events to capture
error_reportedfor conversion errors and divide-by-zero errors from low-privileged accounts. Then, correlate thesql_textwith system object access. - Query text access. In high-security environments, monitor repeated access to query-text and plan-related DMVs. This can be noisy, so start with unusual principals rather than all DBA tooling.
- Default trace and trace permissions. Review who has
ALTER TRACEand who can read trace files. If default trace is not needed, move to controlled Extended Events sessions with explicit targets and retention. - Monitoring-script hygiene, reviewing what your own monitoring code reveals. If a user can see the text of your monitoring query, assume they can also see your allowlists and detection gaps!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
/* XE example: detect possible error-based metadata probing */ CREATE EVENT SESSION [Watch_Metadata_Probing] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.session_id, sqlserver.server_principal_name, sqlserver.database_name, sqlserver.client_hostname, sqlserver.client_app_name, sqlserver.sql_text ) WHERE ( ([error_number] = 245 OR [error_number] = 248 OR [error_number] = 257 OR [error_number] = 529 OR [error_number] = 8114 OR [error_number] = 8134) AND [severity] >= 11 ) ) ADD TARGET package0.event_file ( SET filename = N'C:\SQLAudit\Watch_Metadata_Probing.xel', max_file_size = 100, max_rollover_files = 5 ); GO ALTER EVENT SESSION [Watch_Metadata_Probing] ON SERVER STATE = START; GO /* Find modules that may expose sensitive operational logic in visible text */ SELECT DB_NAME() AS database_name, SCHEMA_NAME(o.schema_id) AS schema_name, o.name AS object_name, o.type_desc FROM sys.sql_modules AS m JOIN sys.objects AS o ON m.object_id = o.object_id WHERE m.definition LIKE '%password%' OR m.definition LIKE '%secret%' OR m.definition LIKE '%token%' OR m.definition LIKE '%sysadmin%' OR m.definition LIKE '%OPENQUERY%' OR m.definition LIKE '%EXECUTE AS%' ORDER BY schema_name, object_name; |
An important distinction to remember
Not every monitoring account is dangerous, and not every DMV is sensitive in the same way. The problem is blanket visibility. Replace broad grants with task-specific access wherever possible, and assume that query text, execution plans, and job definitions may reveal security assumptions that attackers can reuse.
Recommended fixes or mitigations
- Apply least privilege to observability, granting only the permissions required for the specific monitoring function. Do not use
VIEW SERVER STATEas the default answer to every monitoring request! - Use signed modules or controlled wrappers. When a user needs a small set of DMV-derived metrics, expose a sanitized result through a signed stored procedure instead of granting broad DMV access.
- Separate monitoring tiers – a basic health dashboard should not use the same account as deep DBA diagnostics! Use separate accounts, separate permissions, and separate retention.
- Remove secrets from T-SQL text. Never hardcode passwords, tokens, SAS (shared access signature) URLs, sensitive hostnames, or detection allowlists in queries that can be observed by non-administrators.
- Reduce trace exposure. Prefer controlled Extended Events sessions over default trace dependencies. Restrict file-system access to trace/XE (Extended Events) targets.
- Review error handling – and never echo sensitive internal values in custom error messages or
CATCHblocks! Treat errors as a potential disclosure channel.
Move fast. Govern at scale.
SQL Server audit assurance: proving your audit catches indirect access
Auditing is the last line of defense when prevention fails – and is also one of the easiest controls to overestimate. Many environments can show that an audit exists, but far fewer can prove that it captures the actual ways attackers may access the data. The difference between audit configuration and audit assurance is testing.
A common blind spot appears when audit policies are written around a single access path. For example, a DBA may validate that SELECT * FROM dbo.SecureVault generates an audit record, then assume the table is covered.
However, real access paths may involve views, synonyms, stored procedures, dynamic SQL, cross-database three-part names, sparse column sets, computed columns, indexed views, or application modules. If the audit was only tested against the obvious query, it may provide a false sense of security.
SENSITIVE_BATCH_COMPLETED_GROUP can be useful for classified data in SQL Server 2022 (and onwards), but should not be treated as a complete audit strategy by itself. Instead, layer it with schema object access auditing, permission-change auditing, module-execution monitoring, Extended Events, and regular adversarial validation.
An example of this in practice
An organization classifies columns in dbo.SecureVault as highly confidential and enables an audit specification. During validation, the DBA runs a direct SELECT from the same database context, sees the expected audit event, and the compliance task is marked as complete.
Later, a compromised reporting account accesses the same data through a view, synonym, stored procedure, dynamic SQL, cross-database three-part name, or XML column set over sparse columns.
Depending on how the audit was scoped and tested, the indirect path may produce different audit details, may be captured only at the wrapper level, or may not generate the alert the security team expects. The control may technically exist while still failing the detection goal.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
/* Inventory server audits and database audit specifications */ SELECT a.name AS audit_name, a.is_state_enabled AS audit_enabled, a.type_desc AS audit_target_type, s.name AS database_spec_name, s.is_state_enabled AS database_spec_enabled, d.audit_action_name, d.class_desc, d.audited_result FROM sys.server_audits AS a LEFT JOIN sys.database_audit_specifications AS s ON a.audit_guid = s.audit_guid LEFT JOIN sys.database_audit_specification_details AS d ON s.database_specification_id = d.database_specification_id ORDER BY a.name, s.name, d.audit_action_name; /* Inventory server audit specifications */ SELECT a.name AS audit_name, a.is_state_enabled AS audit_enabled, s.name AS server_spec_name, s.is_state_enabled AS server_spec_enabled, d.audit_action_name, d.audited_result FROM sys.server_audits AS a LEFT JOIN sys.server_audit_specifications AS s ON a.audit_guid = s.audit_guid LEFT JOIN sys.server_audit_specification_details AS d ON s.server_specification_id = d.server_specification_id ORDER BY a.name, s.name, d.audit_action_name; |
What are the signs a DBA should look for?
- Audit specifications that rely on one action group only; no complementary coverage for schema access, permission changes, role membership changes, or module execution.
- Sensitive tables exposed through views, synonyms, stored procedures, functions, reporting schemas, computed columns, sparse column sets, or cross-database dependencies, while auditing is tested only against the base table.
- Audits that are enabled but rarely reviewed, never restored from the audit target, and are not integrated with alerting.
- Compliance validation based on the existence of an audit configuration, rather than adversarial testing of access paths.
- Audit targets stored in locations that SQL Server service accounts, local administrators, or privileged database operators can tamper with, free from independent detection.
What to monitor – and how
- Audit the audit configuration. Monitor changes to audits and audit specifications. A disabled audit, changed predicate, changed target path, or dropped specification should alert immediately.
- Test direct and indirect access paths. For each sensitive table, test direct
SELECT, views, stored procedures, dynamic SQL, synonyms, cross-database access, reporting schemas, and sparse XML column sets where applicable. - Layer SQL Audit with XE. Use SQL Audit for durable security evidence, coupled with Extended Events for additional operational context such as
session_id,client_app_name,hostname, and SQL text patterns. - Validate alert semantics, and don’t just check that an audit row exists. Verify that the row contains enough information for the SOC (security operations center) or DBA team to understand what was accessed, by whom, from where, and through which path.
- Review audit retention and integrity. Ensure audit files are written to protected storage, collected centrally, retained long enough, and reviewed for gaps.
The access paths to test – and how to do so
| Access path to test | Why it matters | Expected assurance outcome |
Direct SELECT on sensitive table | Baseline validation only. Not sufficient by itself. | Audit row includes principal, database, object, statement, time, client context, and result. |
| View over sensitive table | Many applications expose data through reporting views. | Audit either captures the base object access or clearly captures the wrapper object and statement. |
| Stored procedure wrapper | Applications may never query the base table directly. | Execution and underlying sensitive access are visible enough for investigation. |
| Dynamic SQL | Attackers often use dynamic execution to change shape and context. | Audit captures the submitted batch/procedure text or complementary XE captures context. |
| Cross-database three-part name | Database-level audit scoping may be misunderstood. | Access from another database context still produces the expected security signal. |
| Synonym | Synonyms hide the target object and can obscure review. | Audit records are usable and identify the path or the resolved target clearly enough. |
| Sparse XML column set | Column-level expectations may not match aggregated XML access. | Sensitive data access still generates a detectable event or compensating alert. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
/* Example validation checklist for one sensitive object */ /* Run with an approved test login in a controlled window. */ -- 1. Direct access SELECT TOP (1) * FROM SensitiveData.dbo.SecureVault; -- 2. Cross-database access from another context USE master; SELECT TOP (1) * FROM SensitiveData.dbo.SecureVault; -- 3. Dynamic SQL EXEC(N'SELECT TOP (1) * FROM SensitiveData.dbo.SecureVault;'); -- 4. Stored procedure wrapper - create/use a controlled test wrapper EXEC SensitiveData.dbo.usp_TestReadSecureVault; -- 5. View or synonym path - where applicable SELECT TOP (1) * FROM Reporting.dbo.vw_SecureVault; SELECT TOP (1) * FROM Reporting.dbo.Syn_SecureVault; /* After each test, confirm that your audit/alert generated the event you expect. */ /* SQL Audit example: monitor changes to audit configuration */ USE master; GO CREATE SERVER AUDIT [Audit_Security_Configuration] TO FILE (FILEPATH = N'C:\SQLAudit\'); GO CREATE SERVER AUDIT SPECIFICATION [Audit_Audit_Changes] FOR SERVER AUDIT [Audit_Security_Configuration] ADD (AUDIT_CHANGE_GROUP), ADD (SERVER_PERMISSION_CHANGE_GROUP), ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), ADD (DATABASE_PERMISSION_CHANGE_GROUP), ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP) WITH (STATE = ON); GO ALTER SERVER AUDIT [Audit_Security_Configuration] WITH (STATE = ON); GO |
Audit design principle
A good audit strategy is defined by whether the security team can reliably answer who accessed the sensitive data, through what path, and from which client. Also: which principal did they use, did the action succeed, and would the event have still been captured if the attacker had used an indirect path?
Recommended fixes or mitigations
- Use layered audit coverage. Combine sensitive-data audit groups with schema object access, schema object changes, permission changes, database role membership changes, server role membership changes, and audit-change monitoring where appropriate.
- Design for indirect access. Audit wrappers, views, synonyms, procedures, and reporting schemas that expose sensitive data.
- Create an audit validation suite with a repeatable set of test queries for each critical data set. Run it after schema changes, permission changes, SQL Server upgrades, and audit policy changes.
- Protect audit targets by writing audit files to protected storage and restricting operating system (OS)-level access. Also forward audit events to a central location where DBAs and SQL service accounts cannot silently modify history.
- Alert on audit tampering. Audit disablement, audit target changes, specification changes, and audit write failures should trigger high-priority alerts.
- Document expected gaps. If a control is compensating rather than complete, document what it does not catch – and what secondary control covers that gap.
In summary: a DBA’s checklist for securing temporary objects, metadata visibility, and audit assurance in SQL Server
- Review all ## usage in SQL Agent jobs, stored procedures, vendor scripts, and application code.
- Remove global temporary objects from privileged workflows or replace them with local temp objects or secured staging tables.
- Review all grants of
VIEW SERVER STATE,VIEW SERVER PERFORMANCE STATE,VIEW ANY DEFINITION,ALTER TRACE, andALTER ANY EVENT SESSION. - Review who can read query text, execution plans, job steps, Extended Events files, trace files, and Query Store data.
- Monitor conversion/divide-by-zero error bursts against system objects as possible metadata probing.
- Remove secrets, tokens, approved-admin allowlists, and sensitive server names from observable T-SQL text.
- Inventory audit specifications and confirm that sensitive-data access is tested through direct and indirect paths.
- Alert on audit configuration changes and ensure audit targets are protected outside the control of the SQL Server service account.
- Treat monitoring and auditing as code: version it, test it, review it, and re-test it after every meaningful change.
Conclusion (the key takeaways)
A global temporary object is not private just because it is temporary, a monitoring permission is not harmless just because it is read-only, and an audit specification is not sufficient just because it exists. Each one must be reviewed in the context of execution scope, privilege, observability, and validation.
As proven in every part of this series, attackers succeed by chaining ordinary features together. Default permissions, privileged roles, msdb jobs, untrusted restores, cross-database trust, triggers, linked servers, temporary objects, DMVs, and audit blind spots are all connected trust boundaries. It only takes one small weakness, in one area, to create danger, as another trusted component may amplify it.
For DBAs, the message is simple: reduce privilege, shared scope, and unnecessary metadata visibility. You should also prevent privileged code consuming untrusted input, and treat an audit as a system that must be tested.
SQL Server can be secured effectively – but only when DBAs move from trusting defaults to continuously verifying what the environment actually allows.
Protect your data. Demonstrate compliance.
FAQs: How to secure temporary objects, metadata visibility, and audit assurance in SQL Server
1. What's the risk with global temporary tables and procedures in SQL Server?
Objects prefixed with ## are visible outside the session that created them. If a privileged process (like an sa-owned SQL Agent job) trusts a predictable global object, a lower-privileged attacker can pre-create it, poison its data, or — in the case of global temp procedures — control the executable code itself.
2. Why are DMVs considered a security risk if they're read-only?
DMVs don’t expose table data, but broad access (e.g., VIEW SERVER STATE) can reveal job names, query text, linked servers, and privileged account names. This metadata gives attackers a reconnaissance map of trust boundaries even without direct data access.
3. How do I know if my SQL Server audit is actually working?
Test more than the obvious path. An audit validated only against a direct SELECT on a sensitive table may miss access through views, synonyms, stored procedures, dynamic SQL, or cross-database three-part names — all of which can bypass the assumptions baked into the original audit design.
4. What's the single most important mindset shift for DBAs here?
Treat these features as operational trust boundaries, not neutral defaults. Ask who can see an object, who can write to it, whether privileged code validates it before use, and whether your audit was tested against indirect access paths — not just the expected one.
5. Where should I start if I only have time to check one thing?
Run a search for ## references across msdb.dbo.sysjobsteps and sys.sql_modules. Global temp objects inside privileged jobs are one of the most concrete, checkable risks covered here.
This document contains proprietary information and is protected by copyright law.
Copyright © 2026 Red Gate Software Limited. All rights reserved
Load comments