Configuring access to SQL Server data and features is an important part of every DBA's job, but one that we all know must be handled carefully. The unintended consequences of giving too much access to resources can mean compromised or lost information. Care must also be taken to manage the permissions of those with legitimate access to the server. With proper measures, those responsible for data entry or table modifications can be protected from themselves, making your job of securing SQL Server resources easier in the long run. There are two important goals to keep in mind when configuring security for SQL Server. We need to protect the integrity of our data and prevent unauthorized access to it. This must be done without losing productivity or unnecessarily tying the hands of those who must work with the data.
This document focuses on each level of security, starting at the server level and working our way down to database objects. They are:
- Server Security
- Database Security
- Schema Security
- Object Security
There are many factors to consider when configuring security on a SQL Server instance. Some of them can be easily overlooked. If a systematic approach is used and all four levels are factored in, the chances of missing something along the way become less likely. There is no such thing as 100% security in any working environment, but paying attention to the details will get everyone close to it.
