Chapter 16. Protecting Your Data from Prying Eyes

Database often contain extremely sensitive information that is valuable to your organization and your customers. In many cases, laws, regulations, or good business practices dictate that you protect that information from disclosure to unauthorized individuals.

In this chapter, I discuss the mechanisms offered in SQL Server 2008 that help you protect your data from unauthorized access. I describe the process of managing database users and roles, grouping objects with schemas, using encryption to protect data in storage and transit, and enabling database auditing to meet compliance requirements.

Creating and Managing Logins

As I discuss in Chapter 2, SQL Server has two different authentication modes: Windows Authentication mode and SQL Server and Windows Authentication (mixed) mode. In either case, you may grant Windows users permission to connect to and manipulate SQL Server databases. If you use mixed mode authentication, you may also create dedicated SQL Server logins that exist only on the database server.

Creating server logins

Creating a database user follows the same basic process, whether you're granting SQL Server permissions to a Windows user or creating a SQL Server login account. Here are the basic steps:

  1. Open SQL Server Management Studio and connect to the SQL Server instance for which you want to create a new login.

  2. Expand the Security folder.

  3. Right-click the Logins folder and select New Login from the pop-up menu.

    SSMS displays ...

Get Microsoft® SQL Server® 2008 For Dummies® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.