Professional SQL Server™ 2005 Administration

Chapter 9. Securing the Database Engine

In this chapter, you learn how to secure your data and the core database engine. As you can imagine, security is a huge topic and can easily turn into an entire book by itself. This chapter covers core topics such as setting up your logins and users for the minimal data access to do their job. This is called the principle of least access. Also, we cover how to do encryption inside T-SQL, which is now part of SQL Server 2005.

Logins

Logins are the essential element for any person who would like to connect to your SQL Server instance. There is a key differentiating factor when a DBA uses the word login versus the word user. A login grants you instance-level rights to an instance, and a user gives you database-level rights to a database. It's similar to the login giving you the key to the front door with no access to any room, unless you have the user key. Certain types of rights at server-level roles will grant you rights to do anything you'd like to do, almost like a skeleton key in a building. We'll cover those server roles in a moment.

Windows vs. SQL Server Authentication

There are two types of logins you can enable in SQL Server: Windows and SQL Server authentication. Windows authentication is the most secure solution for most environments, if you have the luxury of using this type of authentication. With Windows authentication, you would grant the rights to the database to the user's Windows login. A better solution would be to create a group ...