SQL & Database Administration: Comprehensive Revision Notes
Security Best Practices Prefer Windows Authentication over SQL Authentication. Avoid using sa for daily operations. Regularly review and remove unused or orphaned users. Enforce strong passwords and role-based access. Enable auditing, encryption, and backups. Document user and login changes for compliance. Encryption: SQL Server supports multiple types of encryption: Transparent Data Encryption (TDE) for encrypting the whole database at rest, column-level encryption for sensitive columns, Always Encrypted(SQL Server 2016+) for client-side protection so the database never sees plaintext, protecting sensitive data from DBAs, with keys stored locally or in Azure Key Vault, and backup encryption. Proper key management and regular backups of encryption keys are critical for maintaining access and security. ...