How do you monitor SQL Server security continuously?

Last updated 3 November 2026 · DB24 Team

Decide what must never change unnoticed, check it on every instance on a schedule, and record changes as they happen. Most of it is visible inside SQL Server: sysadmin members, logins and permissions, risky settings such as xp_cmdshell and TRUSTWORTHY, linked servers and whether connections are encrypted. A scheduled check can raise the alert; deciding whether a change was allowed, and who should keep access, needs a person.

Key takeaways

SQL Server Audit is the built-in tool, but an audit "is created in a disabled state, and doesn't automatically audit any actions" (Microsoft). Nothing is recorded until someone decides what to audit and where the log goes.
Watch two things: changes as they happen (logins, roles, permissions, configuration) and today's state against an approved baseline.
Changes and deviations can raise alerts automatically. Whether a change was approved, and whether each login still needs its rights, needs a person.
Under NIS2 Article 21 this becomes evidence. In Sweden, MCFFS 2026:11 requires logging of the use of administrative rights, configuration changes in critical systems and permission changes, with logs kept as long as they are needed to investigate (Chapter 4, Section 21).

What should you audit continuously in SQL Server?

Each item can be read from SQL Server itself. The last column says whether a scheduled check can raise the alert without anyone reading a report.

SQL Server security checks: where each shows up and whether it can be alerted on
What to review	Where it shows up in SQL Server	What happens if nobody notices	Can it be alerted on automatically?
Members of sysadmin and securityadmin	sys.server_role_members joined to sys.server_principals	Members of sysadmin "can perform any activity in the server" and cannot be denied permissions. Microsoft says to treat securityadmin as equivalent to sysadmin	Yes, against an approved list
New, changed, disabled or dropped logins	create_date, modify_date and is_disabled in sys.server_principals; SERVER_PRINCIPAL_CHANGE_GROUP in SQL Server Audit	A leaver keeps access, or a login appears that nobody created on purpose	Yes
Role membership changes	SERVER_ROLE_MEMBER_CHANGE_GROUP and DATABASE_ROLE_MEMBER_CHANGE_GROUP	Someone gains rights without a request	Yes
Permission changes, including CONTROL SERVER	sys.server_permissions; SERVER_PERMISSION_CHANGE_GROUP and DATABASE_PERMISSION_CHANGE_GROUP	The same effect as a role change, and easier to miss	Yes
Failed logins	The error log, when login auditing includes failed logins (error 18456, with a state that gives the reason); FAILED_LOGIN_GROUP	Password guessing, or an application with an old password, goes unseen	Yes, above a threshold
Configuration changes	value and value_in_use in sys.configurations; every sp_configure change is written to the error log; the default trace	A setting changes outside the change process	Yes
xp_cmdshell and other surface area options	sys.configurations	xp_cmdshell "Spawns a Windows command shell". Microsoft says it should "generally" be left disabled	Yes, against the baseline
TRUSTWORTHY	is_trustworthy_on in sys.databases. OFF by default, except in msdb	If the owner of a trustworthy database is in sysadmin, the owner "can then create and run unsafe assemblies that can compromise the SQL Server instance"	Yes
Linked servers	sys.servers (is_linked, is_rpc_out_enabled, is_data_access_enabled) and sys.linked_logins (uses_self_credential, remote_name)	Access to one instance reaches another, sometimes with a stored remote login	Yes, for new or changed entries
Unencrypted or outdated connections	encrypt_option and auth_scheme in sys.dm_exec_connections; Force Encryption in SQL Server Configuration Manager	Logins and data cross the network in clear text, or over TLS versions with known weaknesses	Yes, for unencrypted connections
The audit itself and how long it keeps records	status_desc in sys.dm_server_audit_status; AUDIT_CHANGE_GROUP; MAXSIZE and MAX_ROLLOVER_FILES on a file target	An audit that stopped or rolled over has nothing for the period you need	Partly: a stopped audit yes, too short retention no
Whether each login still needs its rights	Logins, role members and permissions per instance, with an owner for each login	Rights pile up and nobody can explain them	No

Where does SQL Server record security events?

SQL Server Audit: a server audit with a server audit specification for instance-level action groups, and database audit specifications per database. The log goes to a file, the Windows Security log or the Windows Application log. All editions support server-level audits, and since SQL Server 2016 SP1 all editions support database-level audits (SQL Server Audit). The action groups in the table are what you add.
The default trace: on by default, "a rich, persistent log of activity and changes primarily related to the configuration options", but marked for removal (default trace).
The error log: login auditing writes failed logins, successful logins or both to the error log, and takes effect after a restart. Every sp_configure change is also written there.
Catalog views: sys.server_principals, sys.server_role_members, sys.server_permissions, sys.configurations, sys.servers and sys.linked_logins hold today's state, to compare with the baseline.
sys.dm_exec_connections: encrypt_option shows whether each current connection is encrypted. Microsoft says to use TLS 1.2, or TLS 1.3 where supported (TLS 1.2 support). From SQL Server 2022 you can also force strict encryption, which uses TDS 8.0 and supports TLS 1.3.

How long should audit logs be kept?

Microsoft sets no period. It says audit logs "must be reviewed and archived periodically to make sure that the target has sufficient space to write more records" (SQL Server Audit). On a file target, MAXSIZE and MAX_ROLLOVER_FILES both default to UNLIMITED: the file "grows until the disk is full". With MAX_ROLLOVER_FILES set, the oldest file is deleted when the limit is passed. ON_FAILURE defaults to CONTINUE, which means SQL Server keeps running when the target cannot be written, and "Audit records aren't retained" (CREATE SERVER AUDIT).

In Sweden, MCFFS 2026:11 requires security logs to be protected against unauthorised access and kept as long as they are needed to investigate security deficiencies. Its general advice is a central system to collect, store and analyse them (Chapter 4, Sections 21–22).

In practice: set the period in your own policy, copy the logs off the instance, and check daily that the audit is still running.

What can raise an alert, and what needs a person?

Every change in the table can raise an alert: a new login, a new sysadmin member, a permission granted, a risky setting turned on, a new linked server, an unencrypted connection, a run of failed logins or an audit that stopped. These steps need someone with the authority to act:

Decide whether a change was approved, and follow up when it was not.
Decide who should hold sysadmin. Microsoft suggests considering CONTROL SERVER for DBA accounts instead, because "The system admin role doesn't respect DENY while CONTROL SERVER does" (security best practices).
Review access on a date: does each login still need its rights, and who owns it.
Remove what nobody can explain. If an old application needs xp_cmdshell, Microsoft recommends to "only enable it for the duration of the actual task that requires it" (xp_cmdshell).
Set how long logs are kept, and investigate real alerts.

How does this connect to NIS2?

NIS2 names no technology. In our reading, security monitoring of SQL Server serves three Article 21(2) areas: incident handling (b), cryptography and encryption (h), and access control (i) (Directive (EU) 2022/2555). In Sweden, MCFFS 2026:11 adds detail in Chapter 4: lock a digital identity after a set number of failed login attempts (Section 15), least privilege (Section 16), security logging (Section 21) and encryption of security logs and authentication credentials in transit (Section 25). For SQL Server logins, the Windows lockout policy applies only when CHECK_POLICY is ON (password policy).

The full requirement-by-requirement checklist is in NIS2 and SQL Server. This page explains the rules as we read them. It is not legal advice.

How DB24 helps

DB24 keeps the security state of every managed instance in one place and alerts when it changes, so a new login or sysadmin member is seen the day it happens rather than at the next review:

Logins and rights per instance: server logins with sysadmin and disabled flags, server roles, permissions and password policy status, and database users with their roles and permissions (integration views).
Changes as they happen: change tracking records created, altered and dropped logins, server role membership changes, and database, schema and object changes, with the time and the login that made them.
Alerts: the Security Audit Alert triggers when new logins or server role changes are collected (notifications).
Risky settings, scored nightly: the AiQ score covers security settings, with parameters such as excessive sysadmin logins, encrypted remote connections and NTLM authentication (release notes 2026.2). The portal marks risky values, such as xp_cmdshell enabled, in red (release notes 2026.1).
Encrypted connections: which connections to each instance use TLS 1.2 or newer, so unencrypted or outdated connections show up before an auditor asks.
Instance settings and the error log: authentication mode, login audit level and whether the sa login is enabled, per instance, and the SQL Server error log collected in one place (integration views).
A record over time: history kept as long as your retention settings say, and DB24's own configuration changes logged with old and new values (audit).

DB24 collects only metadata and performance data, never the contents of your databases.

DB24 is priced per SQL Server instance. Contact us for a quote.

Deciding who should have access, and whether a change was approved, stays with your team; DB24 shows what changed and keeps the record.

To see it on your own instances, contact us.

Frequently asked questions

Is the default trace enough for security auditing?

No. It logs changes mainly related to configuration options, and Microsoft says it will be removed in a future version and recommends Extended Events. SQL Server Audit is built on Extended Events and lets you decide exactly what to record.

Which SQL Server editions support SQL Server Audit?

All editions support server-level audits. Since SQL Server 2016 SP1, all editions also support database-level audits; before that, only Enterprise, Developer and Evaluation did.

Can a sysadmin hide their own changes?

They can change the audit. Microsoft: "Principals in the sysadmin role can tamper with any audit component." Include AUDIT_CHANGE_GROUP, restrict access to the audit files, keep copies away from the instance and check that the audit is running. In each database, sysadmin members appear as dbo, so audit dbo.

How do we see failed logins?

Turn on login auditing for failed logins in the server properties. It writes to the error log and takes effect after a restart. The message the client receives hides the reason, but the error log entry for error 18456 has a state that shows it (error 18456). For a fuller record, add FAILED_LOGIN_GROUP to a server audit specification.

How much does DB24 cost?

DB24 is priced per SQL Server instance. Contact us for a quote.

COMMUNITY

Follow us

GUIDES

DB24 Docs

Articles

FEATURED

LISTEN TO ROGER

SQL Server guides

NIS2 and SQL Server

SQL Server without a DBA

Backup monitoring

Support dates

Alternatives compared

Security auditing

Automation across instances