Securing a system requires efforts that can be dropped into 3 buckets: Configuration, Alerting, and Auditing. (Am I missing any? Post a comment if you think of any others...)
Securing a system's configuration means its run-time configuration. Design-time is irrelevant except in how it impacts the run-time configuration, as it is only the actual run-time configuration (including any run-time changes) that creates vulnerabilities.
System alerting provides both preventive and reactive capabilities that can be used to improve security or performance.
System auditing provides both preventive and forensic capabilities that can be used to detect and reduce vulnerabilities before they're breached, or to investigate after a breach has occurred.
Naturally, all of these play a big role in the NSA SQL Server security guidelines, the Dept. of Defense's Database STIG, and other SQL Server security specifications.
For this tip, I'd like to call your attention to SQL Server 2008's Auditing, which is a game-changer. Forget traces, event notifications, DDL triggers, trigger-populated shadow tables, and all the rest. SQL Server Audit is the primary solution from now on. It's easier to configure, it can be configured at a more granular level, and it has better performance.
SQL Server Audit performs its actions at the time permissions are checked, so SQL Server only has to evaluate audited tasks, not every task as it does with traces. In addition, auditing can be asynchronous in addition to synchronous. And audits can be fully scripted in T-SQL, so it's very easy to apply to multiple servers, and they can be monitored by Policy-Based Management (PBM). You can even store the audit info in the server's Security Log in addition to the Application Log or binary files, but binary files give you the best performance and greatest query flexibility.
Ready to get started? Read the Auditing in SQL Server 2008 whitepaper and practice. Do you prefer tinkering to reading? Then here's a tip for you: Create an "audit" at the server level first (defining where the target where the audited information will be stored), then you can create a server-level or database-level "audit specification". Auditing won't begin until you enable the audit and at least one audit specification.
Whitepaper: Auditing in SQL Server 2008: http://msdn.microsoft.com/en-us/library/dd392015.aspx
NSA security guidelines for databases: http://www.nsa.gov/ia/guidance/security_configuration_guides/database_servers.shtml