--- title: "SQL Server security auditing: what to monitor | DB24" url: https://db24.ai/sql-server-security-auditing description: "What to audit continuously in SQL Server: logins, sysadmin, role and configuration changes, risky settings and encryption, and what can raise an alert." lang: en --- # 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 (https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine)). 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. | 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 (https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine)). The action groups (https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions) 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 (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/default-trace-enabled-server-configuration-option)). - **The error log:** login auditing (https://learn.microsoft.com/en-us/ssms/configure-login-auditing-sql-server-management-studio) writes failed logins, successful logins or both to the error log, and takes effect after a restart. Every sp_configure (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql) change is also written there. - **Catalog views:** sys.server_principals (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql), sys.server_role_members (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-role-members-transact-sql), sys.server_permissions (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql), sys.configurations (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-configurations-transact-sql), sys.servers (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-servers-transact-sql) and sys.linked_logins (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-linked-logins-transact-sql) hold today's state, to compare with the baseline. - **sys.dm_exec_connections:** encrypt_option (https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-connections-transact-sql) shows whether each current connection is encrypted. Microsoft says to use TLS 1.2, or TLS 1.3 where supported (TLS 1.2 support (https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-1-2-support-microsoft-sql-server)). From SQL Server 2022 you can also force strict encryption (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption), which uses TDS 8.0 (https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8) 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 (https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine)). 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 (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql)). 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: 1. Decide whether a change was approved, and follow up when it was not. 2. 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 (https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-server-security-best-practices)). 3. Review access on a date: does each login still need its rights, and who owns it. 4. 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 (https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option)). 5. 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 (https://eur-lex.europa.eu/legal-content/EN/TXT/HTML/?uri=CELEX:32022L2555)). In Sweden, MCFFS 2026:11 (https://www.ncsc.se/siteassets/radgivning-och-stod/foreskrifter/mcffs-2026-11-foreskrifter-och-allmanna-rad-om-sakerhetsatgarder-och-ledningens-utbildning-for-vasentliga-och-viktiga-verksamhetsutovare.pdf) 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 (https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy)). The full requirement-by-requirement checklist is in NIS2 and SQL Server (https://db24.ai/nis2-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 (https://db24.ai/docs/integration)). - **Changes as they happen:** change tracking (https://db24.ai/docs/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 (https://db24.ai/docs/notifications)). - **Risky settings, scored nightly:** the AiQ score (https://db24.ai/docs/aiq) covers security settings, with parameters such as excessive sysadmin logins, encrypted remote connections and NTLM authentication (release notes 2026.2 (https://db24.ai/docs/version-2026-2)). The portal marks risky values, such as xp_cmdshell enabled, in red (release notes 2026.1 (https://db24.ai/docs/version-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 (https://db24.ai/docs/integration)). - **A record over time:** history kept as long as your retention settings (https://db24.ai/docs/data-retention) say, and DB24's own configuration changes logged with old and new values (audit (https://db24.ai/docs/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 (https://db24.ai/contact). ## 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 (https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error)). For a fuller record, add FAILED_LOGIN_GROUP to a server audit specification. ### How much does DB24 cost? ## Sources 1. Microsoft Learn, SQL Server Audit (Database Engine): https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine 2. Microsoft Learn, SQL Server Audit action groups and actions: https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions 3. Microsoft Learn, CREATE SERVER AUDIT: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-server-audit-transact-sql 4. Microsoft Learn, sys.dm_server_audit_status: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-server-audit-status-transact-sql 5. Microsoft Learn, Default trace enabled: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/default-trace-enabled-server-configuration-option 6. Microsoft Learn, Configure login auditing: https://learn.microsoft.com/en-us/ssms/configure-login-auditing-sql-server-management-studio 7. Microsoft Learn, MSSQLSERVER_18456: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error 8. Microsoft Learn, sp_configure: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-configure-transact-sql 9. Microsoft Learn, sys.configurations: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-configurations-transact-sql 10. Microsoft Learn, sys.server_principals: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-principals-transact-sql 11. Microsoft Learn, sys.server_role_members: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-role-members-transact-sql 12. Microsoft Learn, sys.server_permissions: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql 13. Microsoft Learn, Server-level roles: https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles 14. Microsoft Learn, xp_cmdshell server configuration option: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option 15. Microsoft Learn, xp_cmdshell (Transact-SQL): https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql 16. Microsoft Learn, TRUSTWORTHY database property: https://learn.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property 17. Microsoft Learn, sys.servers: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-servers-transact-sql 18. Microsoft Learn, sys.linked_logins: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-linked-logins-transact-sql 19. Microsoft Learn, sys.dm_exec_connections: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-objects/sys-dm-exec-connections-transact-sql 20. Microsoft Learn, TLS 1.2 support for Microsoft SQL Server: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/tls-1-2-support-microsoft-sql-server 21. Microsoft Learn, Configure SQL Server Database Engine for encrypting connections: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption 22. Microsoft Learn, TDS 8.0: https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8 23. Microsoft Learn, Password policy: https://learn.microsoft.com/en-us/sql/relational-databases/security/password-policy 24. Microsoft Learn, SQL Server security best practices: https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-server-security-best-practices 25. Directive (EU) 2022/2555 (NIS 2), EUR-Lex: https://eur-lex.europa.eu/legal-content/EN/TXT/HTML/?uri=CELEX:32022L2555 26. MCFFS 2026:11, security measures and management training (PDF): https://www.ncsc.se/siteassets/radgivning-och-stod/foreskrifter/mcffs-2026-11-foreskrifter-och-allmanna-rad-om-sakerhetsatgarder-och-ledningens-utbildning-for-vasentliga-och-viktiga-verksamhetsutovare.pdf 27. DB24 Docs, Change Tracking: https://db24.ai/docs/change-tracking 28. DB24 Docs, Notifications: https://db24.ai/docs/notifications 29. DB24 Docs, Integration: https://db24.ai/docs/integration ## Structured data ```json [ { "@context": "https://schema.org", "@type": "Organization", "name": "DB24", "url": "https://db24.ai", "logo": "https://db24.ai/logo.png", "description": "SQL Server on autopilot. DB24 monitors, backs up and documents SQL Server automatically for organisations that run it without a dedicated DBA.", "contactPoint": { "@type": "ContactPoint", "contactType": "sales", "email": "sales@db24.ai" }, "sameAs": [ "https://www.linkedin.com/company/db24" ] }, { "@context": "https://schema.org", "@type": "Article", "headline": "How do you monitor SQL Server security continuously?", "description": "What to audit continuously in SQL Server: logins, sysadmin, role and configuration changes, risky settings and encryption, and what can raise an alert.", "datePublished": "2026-11-03", "dateModified": "2026-11-03", "inLanguage": "en", "author": { "@type": "Organization", "name": "DB24", "url": "https://db24.ai" }, "publisher": { "@type": "Organization", "name": "DB24", "logo": { "@type": "ImageObject", "url": "https://db24.ai/logo.png" } }, "image": [ "https://db24.ai/og-image.png" ], "mainEntityOfPage": { "@type": "WebPage", "@id": "https://db24.ai/sql-server-security-auditing" } }, { "@context": "https://schema.org", "@type": "FAQPage", "mainEntity": [ { "@type": "Question", "name": "Is the default trace enough for security auditing?", "acceptedAnswer": { "@type": "Answer", "text": "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." } }, { "@type": "Question", "name": "Which SQL Server editions support SQL Server Audit?", "acceptedAnswer": { "@type": "Answer", "text": "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." } }, { "@type": "Question", "name": "Can a sysadmin hide their own changes?", "acceptedAnswer": { "@type": "Answer", "text": "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." } }, { "@type": "Question", "name": "How do we see failed logins?", "acceptedAnswer": { "@type": "Answer", "text": "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." } }, { "@type": "Question", "name": "How much does DB24 cost?", "acceptedAnswer": { "@type": "Answer", "text": "DB24 is priced per SQL Server instance. Contact us for a quote." } } ] }, { "@context": "https://schema.org", "@type": "BreadcrumbList", "itemListElement": [ { "@type": "ListItem", "position": 1, "name": "Home", "item": "https://db24.ai/" }, { "@type": "ListItem", "position": 2, "name": "SQL Server security auditing", "item": "https://db24.ai/sql-server-security-auditing" } ] } ] ```