SQL Server Security

Security Audit Workflow

Follow these numbered steps for a full security assessment.

1. Check authentication mode — Windows-only preferred.
2. Audit sysadmin members — should be minimal named accounts, no service accounts.
3. Review db_owner memberships — across all user databases.
4. Check dangerous server permissions — CONTROL SERVER, ALTER ANY LOGIN.
5. Identify SQL logins with weak policy settings — no expiration or policy check.
6. Assess surface area — xp_cmdshell, CLR, OLE Automation, linked servers.
7. Verify encryption — TDE for data at rest, TLS for connections.
8. Review audit configuration — failed logins at minimum; sensitive tables audited.
9. Check TRUSTWORTHY databases — should be OFF on all user databases.

Top Security Vulnerabilities with Detection Queries

1. Authentication mode and SA account

-- 1 = Windows auth only (preferred), 0 = Mixed mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;

-- SA should be DISABLED and renamed
SELECT name, is_disabled, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE name IN ('sa', 'SA');

Remediation:

ALTER LOGIN sa DISABLE;
ALTER LOGIN sa WITH NAME = [sql_admin_renamed];  -- Obfuscate the well-known name

2. Sysadmin members

SELECT m.name AS member_name, m.type_desc, m.is_disabled
FROM sys.server_role_members srm
JOIN sys.server_principals r ON srm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON srm.member_principal_id = m.principal_id
WHERE r.name = 'sysadmin'
ORDER BY m.type_desc, m.name;
-- All members should be named human DBA accounts; no application accounts

3. db_owner members across all databases

EXEC sp_MSforeachdb '
USE [?];
SELECT DB_NAME() AS db_name, dp.name AS member_name, dp.type_desc
FROM sys.database_role_members drm
JOIN sys.database_principals r  ON drm.role_principal_id  = r.principal_id
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
WHERE r.name = ''db_owner'' AND dp.name NOT IN (''dbo'')';

4. Dangerous server-level permissions

SELECT
    sp.name           AS login_name,
    sp.type_desc,
    srvp.permission_name,
    srvp.state_desc
FROM sys.server_permissions srvp
JOIN sys.server_principals sp ON srvp.grantee_principal_id = sp.principal_id
WHERE srvp.permission_name IN (
    'CONTROL SERVER', 'ALTER ANY LOGIN',
    'ALTER ANY DATABASE', 'IMPERSONATE ANY LOGIN'
)
ORDER BY sp.name;

5. SQL logins with weak policy

SELECT name, is_policy_checked, is_expiration_checked, is_disabled,
       create_date, modify_date
FROM sys.sql_logins
WHERE is_policy_checked = 0 OR is_expiration_checked = 0;
-- Enforce: ALTER LOGIN [name] WITH CHECK_POLICY = ON, CHECK_EXPIRATION = ON;

6. TRUSTWORTHY databases

SELECT name, is_trustworthy_on
FROM sys.databases
WHERE is_trustworthy_on = 1;
-- Only msdb should be TRUSTWORTHY; disable on all user databases:
-- ALTER DATABASE [YourDB] SET TRUSTWORTHY OFF;

7. Cross-database ownership chaining

SELECT name, is_db_chaining_on FROM sys.databases WHERE is_db_chaining_on = 1;
-- Fix: ALTER DATABASE [YourDB] SET DB_CHAINING OFF;
-- Instance level:
SELECT name, value_in_use FROM sys.configurations
WHERE name = 'cross db ownership chaining';

Permission Review

All database-level permissions

SELECT
    dp.name           AS principal_name,
    dp.type_desc      AS principal_type,
    o.name            AS object_name,
    o.type_desc       AS object_type,
    p.permission_name,
    p.state_desc      AS permission_state
FROM sys.database_permissions p
LEFT JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o              ON p.major_id = o.object_id
ORDER BY dp.name, o.name, p.permission_name;

Orphaned users (login no longer exists)

SELECT dp.name AS user_name, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')
  AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
  AND sp.name IS NULL;

-- Fix orphaned user by mapping to correct login
ALTER USER [OrphanedUser] WITH LOGIN = [MatchingLogin];

Effective permissions for a user

EXECUTE AS USER = 'AppUser';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;

TDE Setup (5 Steps)

Transparent Data Encryption encrypts data files, log files, and backups at rest. It is transparent to applications.

Step 1 — Create master key in master database

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPass!';

Step 2 — Create TDE certificate

CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate for Production';

Step 3 — Back up the certificate (MANDATORY — losing this key = permanent data loss)

BACKUP CERTIFICATE TDECert
TO FILE = 'D:\Certs\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'D:\Certs\TDECert.pvk',
    ENCRYPTION BY PASSWORD = 'CertBackupPass!'
);
-- Store this backup OFF the server in a secure vault

Step 4 — Create database encryption key

USE [YourDatabase];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

Step 5 — Enable TDE and monitor progress

ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;

-- Monitor encryption progress
SELECT
    DB_NAME(database_id)    AS database_name,
    encryption_state_desc,
    percent_complete
FROM sys.dm_database_encryption_keys;
-- encryption_state 3 = Encrypted (complete)

Surface Area Reduction

Disable features that are not in use. Each enabled feature is an attack surface.

-- Disable xp_cmdshell (OS command execution from SQL)
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;

-- Disable OLE Automation Procedures
EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;

-- Disable CLR (if not using CLR assemblies)
EXEC sp_configure 'clr enabled', 0; RECONFIGURE;

-- Disable remote admin connections (DAC accessible locally only)
EXEC sp_configure 'remote admin connections', 0; RECONFIGURE;

-- Disable cross-database ownership chaining at instance level
EXEC sp_configure 'cross db ownership chaining', 0; RECONFIGURE;

-- Verify current configuration
SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (
    'xp_cmdshell', 'Ole Automation Procedures', 'clr enabled',
    'remote admin connections', 'cross db ownership chaining'
);

Check linked servers (often a privilege escalation path)

SELECT name, product, provider, data_source,
       is_remote_login_enabled, is_rpc_out_enabled
FROM sys.servers
WHERE is_linked = 1;
-- Audit each linked server: who can use it and what permissions does it run under?

Verify TLS encryption on connections

SELECT session_id, encrypt_option, net_transport, client_net_address
FROM sys.dm_exec_connections
WHERE encrypt_option = 'FALSE';
-- Any FALSE = unencrypted connection. Enforce encryption via SQL Server Configuration Manager.

SQL Server Audit Configuration

Audit captures who did what and when. At minimum, audit failed logins.

-- Step 1: Create server audit (write to file)
CREATE SERVER AUDIT [DBA_SecurityAudit]
TO FILE (
    FILEPATH       = N'D:\Audit\',
    MAXSIZE        = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

ALTER SERVER AUDIT [DBA_SecurityAudit] WITH (STATE = ON);

-- Step 2: Create server audit specification
CREATE SERVER AUDIT SPECIFICATION [DBA_ServerAuditSpec]
FOR SERVER AUDIT [DBA_SecurityAudit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON);

-- Step 3: Query audit log
SELECT event_time, action_id, succeeded, server_principal_name,
       database_name, object_name, statement
FROM sys.fn_get_audit_file('D:\Audit\DBA_SecurityAudit*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

Least-Privilege Application Access Pattern

Never grant db_datareader / db_datawriter directly. Use custom roles scoped to what the application needs.

-- Create a minimal-permission application role
CREATE ROLE [AppRole_Orders];
GRANT SELECT ON dbo.Orders   TO [AppRole_Orders];
GRANT SELECT ON dbo.Customers TO [AppRole_Orders];
GRANT EXECUTE ON dbo.usp_PlaceOrder TO [AppRole_Orders];
GRANT INSERT ON dbo.Orders TO [AppRole_Orders];

-- Add the application user to the role only
CREATE USER [AppServiceUser] FOR LOGIN [DOMAIN\AppServiceAccount];
ALTER ROLE [AppRole_Orders] ADD MEMBER [AppServiceUser];
-- No db_datareader, no db_owner, no sysadmin

References

• Permissions model — full permission hierarchy, GRANT/DENY/REVOKE syntax
• Encryption guide — TDE and Always Encrypted setup steps
• Audit configuration — SQL Server Audit setup and querying
• Examples — security audit findings, fixing orphaned users, enabling TDE, configuring login auditing