Part of the DBA-Tools Project.
Who Can Do What, What’s Actually Happening, and What’s Silently Missing
Security on an inherited SQL Server instance breaks down into three genuinely different questions, and a single “run a security scan” checklist tends to blur them together. Who has privileged access, right now? What’s actually happening at the login and access layer, brute-force attempts, weak settings, active sessions? And what security infrastructure, auditing, DDL protection, encryption lifecycle, either doesn’t exist yet or exists silently and gets forgotten?
This post is the map across all three, tying together every DBA Scripts post on security into one place: what each answers, how they fit together, and the order to run them in when reviewing a server’s security posture from zero.
Why This Matters
- Sysadmin membership is the headline question, but privilege lives at every level, server roles beyond sysadmin, database roles, explicit object grants, all commonly overlooked once the sysadmin check comes back clean
- Failed login patterns, weak password policy, and an enabled
saaccount are all things that make a compromise easier, not evidence that one has happened, catching them early is prevention, not incident response - Certificates back TDE, Service Broker, and column encryption, and they all share the same failure mode: nobody notices an expiring one until a restore fails on the wrong server
- SQL Server Audit, DDL triggers, and Agent proxy/credential mappings are the kind of infrastructure that either was never set up or was set up once and forgotten, neither shows up in a routine health check
The Scripts, Grouped by Question
Who has privileged access right now?
- Sysadmin Members, the single highest-stakes question, focused and fast
- Permissions and Role Membership, everything underneath sysadmin: explicit object/schema grants, server-level denies, database roles, every other server role
- User Permissions Audit, a consolidated summary view across logins, users, and role memberships
- Orphaned Users, database users whose login SID no longer matches any server login, a common post-restore/migration gap
What’s actually happening at the login layer?
- Login Security Audit, failed login patterns and lockout state, current session activity, and weak password policy or
sastatus in one cluster
Is the encryption layer healthy?
- Certificates, Keys, and TDE Status, every certificate’s expiry, what it’s used for, and TDE encryption state across every database
What security infrastructure is missing or hidden?
- Audit Specifications, DDL Triggers, and Proxy Credentials, SQL Server Audit compliance gaps, hidden schema-change triggers, and SQL Agent privilege-escalation paths
- Database Mail and xp_cmdshell Configuration, surface-area settings that expand what a compromised login can actually do
How They Fit Together
Run these roughly in this order when reviewing a server’s security posture from scratch:
- Sysadmin Members, then Permissions and Role Membership, establishes who has privileged access at every level, not just the top
- Orphaned Users and User Permissions Audit, establishes whether the access that exists is clean and intentional, not leftover from a migration
- Login Security Audit, establishes what’s actually happening right now: failed attempts, active sessions, weak settings
- Certificates, Keys, and TDE Status, establishes whether the encryption layer will survive a restore or DR failover
- Audit Specifications, DDL Triggers, and Proxy Credentials, establishes what’s missing entirely: formal auditing, hidden schema protection, privilege-escalation paths through Agent proxies
Each step narrows from “who can do things” to “what’s actually happening” to “what’s silently absent.” A server that passes step 1 cleanly can still fail steps 3 through 5 badly, they’re genuinely different questions.
Best Practices Across the Series
- Run the full sequence on any inherited server, sysadmin alone is necessary but never sufficient
- Treat
SA_ENABLED, an unconfigured SQL Server Audit, and an expiring TDE certificate as the three highest-priority individual findings across this whole series, act on them same-week, not on a rolling backlog - Re-run the Login Security Audit cluster on a routine schedule, it’s the one most likely to change week to week
- Cross-reference TDE findings against Edition Feature Usage before any edition downgrade, TDE is Enterprise-only and blocks a downgrade outright
See Also
This pillar is one of ten in DBA Scripts: The Complete Guide, the map across the whole series organized by the question you’re actually asking.
Summary
Security isn’t one checklist, it’s three separate questions that need three separate answers: who has access, what’s actually happening with that access, and what protective infrastructure is missing entirely. This series covers all three, from the fast sysadmin check to the deepest audit-and-encryption review, so a security pass on an unfamiliar server means working through a known sequence, not guessing at what to check next.
Start with Sysadmin Members and Permissions and Role Membership on any new server, then work down to login activity, encryption, and the audit/DDL/proxy layer as the review calls for it.
Leave a Reply