Professional Microsoft® SQL Server® 2008 Administration

9.1. Security Principles

When it comes to securing any system, several security principles provide the best results. The ones we talk about with respect to securing your SQL Server are as follows:

• Principle of least privilege

• The CIA triad

• Defense in depth

9.1.1. The Principle of Least Privilege

The principle of least privilege is a security best practice which states that a person should be given only the rights he or she needs to do the job, and no more. The reasoning behind this is simple: If a person makes a mistake, the amount of damage that can be done is limited. If the person is malicious, or if a malicious person compromises another individual's account, granting the original person only the rights needed to do the job will limit the potential impact of an attack. In a classic scenario, suppose a user needs read access to a few tables in a database in order to create reports for the executive staff. The "easy" solution is to make the user a member of the db_datareader fixed database role. Unfortunately, this role gives read access to all tables and views in a database. Therefore, the user would have access to more data than he or she needs. Following the principle of least privilege, a database role should be created with SELECT permission granted against only the tables the user needs. The user should then be made a member of this new role.

The principle of least privilege also applies to applications, protocols, and services. For instance, it should be extended to service ...