62 Chapter 2 Configuring SQL Server 2005
2. You are in charge of designing the physical structure for your company’s new
server running SQL Server 2005. The server has the following characteristics:
two disks in RAID 1, five disks in RAID 5, and another ten disks in RAID 5.
Where should you store database files for the best performance?
A. Use RAID 1 to install the operating system. Use the first RAID 5 disk set to
install SQL Server executable files and the second RAID 5 disk set to store
database files.
B. Use RAID 1 to install the operating system. Use the first RAID 5 system to
install SQL Server executable files and data and transaction log files. Use
the second RAID 5 system to store database backups.
C. Use RAID 1 to install the operating system and SQL Server executable files.
Use the first RAID 5 system to store transaction log files. Use the second
RAID 5 system to store data files.
D. Use the first RAID 5 system to install the operating system and SQL Server
executable files. Store data files in the second RAID 5 system and log files
in the RAID 1 system.
3. Which of the following are valid filegroup types? (Choose all that apply.)
A. Read-only
B. Write-only
C. Default
D. Primary
C0262271X.fm Page 62 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 63
Lesson 2: Configuring Database Mail
Database Mail is a new solution for sending messages from the SQL Server 2005
database engine. Applications that are configured to use Database Mail can send e-mail
messages, including HTML messages, query results, and file attachments, to users.
Database Mail uses the Simple Mail Transfer Protocol (SMTP) and does not require
you to install any Extended MAPI client, such as Microsoft Office Outlook, on SQL
Server.

After this lesson, you will be able to:
■ Identify Database Mail prerequisites.
■ Understand the Database Mail architecture.
■ Configure the SQL Server Database Mail subsystem.
Estimated lesson time: 15 minutes
Identifying Database Mail Prerequisites
Before you configure Database Mail, you need to review the following prerequisites:
■ Database Mail must be enabled. Database Mail is not enabled by default; you
need to enable it by using the SQL Server Surface Area Configuration tool, the
Database Mail Configuration Wizard, or the sp_configure stored procedure.
■ Service Broker needs to be enabled in the Database Mail host database. The
default Database Mail host database is msdb, and Service Broker is enabled on
msdb by default.
MORE INFO Service Broker
You can get a full explanation about Service Broker from />default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp.
■ The Database Mail external executable needs access to the SMTP server. If the
SMTP server requires authentication, the executable accesses the SMTP server
by using the SQL Server service account credentials by default. You should
ensure that the SQL Server service account can access the SMTP server.
C0262271X.fm Page 63 Friday, April 29, 2005 7:29 PM
64 Chapter 2 Configuring SQL Server 2005
Understanding the Database Mail Architecture
Database Mail has four main components: configuration components, messaging
components, the executable, and logging and auditing components.
■ Configuration components There are two configuration components:
❑ A Database Mail account contains the information that SQL Server uses to
send e-mail messages to the SMTP server, such as the SMTP server name,
the authentication type, and the e-mail address.
❑ A Database Mail profile is a collection of Database Mail accounts. Applica-
tions use Database Mail profiles to send e-mail messages so that the infor-

mation about the accounts is transparent for applications, which lets DBAs
change account information without modifying applications’ stored proce-
dures. Database Mail profiles can be private or public. For a private profile,
Database Mail maintains a list of users that can use the profile. For a public
profile, members of the msdb database role DatabaseMailUserRole can use
the profile.
■ Messaging components The main messaging component is the Database Mail
host database, which contains all the Database Mail objects. The Database Mail
host database is msdb.
■ Database Mail executable To minimize the impact on SQL Server, Database Mail
uses an external executable to process e-mail messages. The executable, called
DatabaseMail90.exe, is located in the MSSQL\Binn directory in the SQL Server
installation path. Database Mail uses Service Broker activation to start the exter-
nal program when there are e-mail messages waiting to be processed. The exter-
nal program connects to the database engine by using Microsoft Windows
authentication with the SQL Server service account credentials.
■ Logging and auditing components Database Mail stores log information in
tables in the Database Mail host database. You can see this log information from
the Database Mail Log or by querying the sysmail_event_log system view.
How to Configure Database Mail
SSMS provides the Database Mail Configuration Wizard for configuring your Database
Mail environment. You can set up Database Mail; manage accounts, profiles, and secu-
rity; and change system parameters from the wizard, which is shown in Figure 2-1.
C0262271X.fm Page 64 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 65
Figure 2-1 Database Mail Configuration Wizard
In the following example, you have an SMTP mail server called mail.adventure-
works.com and an account on that server with an e-mail address of sql@adventure-
works.com. To configure a Database Mail profile account for this e-mail account, follow
these steps:

1. Expand the Management node within Object Explorer in SSMS.
2. Right-click Database Mail and select Configure Database Mail. The Welcome
page of the Database Mail Configuration Wizard appears. Click Next.
3. On the Select Configuration Task page, verify that Set Up Database Mail By Per-
forming The Following Tasks is selected and click Next.
4. A warning message appears: The Database Mail feature Is Not Available. Would
You Like To Enable This Feature? Click Yes.
5. In the Profile Name text box, type TestProfile and click Add to add a new SMTP
account.
6. The New Database Mail Account dialog box appears. Fill in the text boxes as Fig-
ure 2-2 shows. Click OK and then click Next.
C0262271X.fm Page 65 Friday, April 29, 2005 7:29 PM
66 Chapter 2 Configuring SQL Server 2005
Figure 2-2 New Database Mail Account dialog box
7. In the resulting Manage Profile Security page, you configure public and private
profiles. Select the TestProfile check box and click Next.
8. The Configure System Parameters page appears, which enables you to change
system-level configurations. Leave the default options and click Next. The Com-
plete The Wizard page appears. Click Finish.
You can also accomplish these tasks by using the Database Mail stored procedures.
For example, you can change configuration information by using the
sysmail_configure_sp stored procedure.
MORE INFO Database Mail stored procedures
For a list of Database Mail stored procedures and what they do, see the “Database Mail and SQL
Mail Stored Procedures (Transact-SQL)” topic in SQL Server 2005 Books Online.
NOTE Viewing configuration options
You can view information about Database Mail configuration options by running the Database Mail
Wizard or by executing the sysmail_help_configure_sp msdb stored procedure.
C0262271X.fm Page 66 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 67

PRACTICE Configuring Database Mail
In this practice, you will use the Database Mail stored procedures to configure Data-
base Mail so that you can send e-mail messages from SQL Server. You will create a
Database Mail public profile for an SMTP mail account. The SMTP server is
mail.Adventure-Works.com, and the e-mail address is
NOTE Example server name and e-mail address in this code
SMTP server names and account e-mail addresses used in this code are examples. You should
change them to a valid SMTP server name and e-mail address to run the code.
1. Execute the sysmail_add_account procedure as follows to create a Database Mail
account, using mail.Adventure-works.com as the mail server and sql@adventure-
works.com as the e-mail account:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'AdventureWorks Mail',
@description = 'Mail account for Database Mail.',
@email_address = '',
@display_name = 'AdventureWorks Automated Mailer',
@mailserver_name = 'mail.Adventure-Works.com'
2. Use the sysmail_add_profile procedure to create a Database Mail profile called
AdventureWorks Mail Profile:
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'AdventureWorks Mail Profile',
@description = 'Profile used for database mail.'
3. Execute the sysmail_add_profileaccount procedure to add the Database Mail
account you created in step 1 to the Database Mail profile you created in step 2:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'AdventureWorks Mail Profile',
@account_name = 'AdventureWorks Mail',
@sequence_number = 1
4. Use the sysmail_add_principalprofile procedure to grant the Database Mail pro-
file access to the msdb public database role and to make the profile the default

Database Mail profile:
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'AdventureWorks Mail Profile',
@principal_name = 'public',
@is_default = 1 ;
C0262271X.fm Page 67 Friday, April 29, 2005 7:29 PM
68 Chapter 2 Configuring SQL Server 2005
Lesson Summary
■ Database Mail is the SQL Server 2005 subsystem that lets you send e-mail mes-
sages from database applications.
■ Database Mail does not need any Extended MAPI client installed on SQL Server
because the mail subsystem sends messages directly to an SMTP server.
■ You need to have Service Broker enabled to use Database Mail, which uses an
external executable to send messages.
■ You can configure multiple Database Mail accounts and group them into Data-
base Mail profiles.
■ All Database Mail information is stored in the msdb database, the default Data-
base Mail host database.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review
them in electronic form.
NOTE Answers
Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which of the following is a prerequisite for Database Mail?
A. Service Broker
B. Database Mirroring
C. Extended MAPI Profile
D. Microsoft Exchange Server

C0262271X.fm Page 68 Friday, April 29, 2005 7:29 PM
Lesson 2: Configuring Database Mail 69
2. Which of the following sentences is true for authentication mechanisms when
the SMTP server is being accessed?
A. Database Mail accesses the SMTP server using the database engine service
credentials by default.
B. Database Mail accesses the SMTP server using the SQL Server Agent service
credentials by default.
C. Database Mail accesses the SMTP server using the SQL Browser service cre-
dentials by default.
D. Database Mail accesses the SMTP server using the SQL Server Active Direc-
tory Helper service credentials by default.
3. Which of the following sentences is true for Database Mail?
A. A Database Mail account is a collection of Database Mail profiles.
B. Each Mail Database Host user account must have a Database Mail profile
associated.
C. A Database Mail profile is a collection of Mail Database Host user accounts.
D. A Database Mail profile is a collection of Database Mail accounts.
C0262271X.fm Page 69 Friday, April 29, 2005 7:29 PM
70 Chapter 2 Configuring SQL Server 2005
Lesson 3: Specifying a Recovery Model
A recovery model is a database configuration option that controls how transactions are
logged, whether the transaction log is backed up, and what restore options are avail-
able for the database. The recovery model you choose for your database has both data-
recovery implications and performance implications, based on the logging the recov-
ery model performs or doesn’t perform.
After this lesson, you will be able to:
■ Explain the differences between the recovery models.
■ Choose the best recovery model for each SQL Server 2005 database.
Estimated lesson time: 10 minutes

Recovery Models Overview
SQL Server 2005 provides three recovery models for databases: Full, Simple, and
Bulk-Logged. These models determine how SQL Server works with the transaction
log and selects the operations that it logs and whether it truncates the log. Truncating
the transaction log is the process of removing committed transactions and leaving log
space to new transactions. The following is a definition of each recovery model:
■ In the Full recovery model, the database engine logs all operations onto the trans-
action log, and the database engine never truncates the log. The Full recovery
model lets you restore a database to the point of failure (or to an earlier point in
time in SQL Server 2005 Enterprise Edition).
■ In the Simple recovery model, the database engine minimally logs most operations
and truncates the transaction log after each checkpoint. In the Simple recovery
model, you cannot back up or restore the transaction log. Furthermore, you can-
not restore individual data pages.
IMPORTANT Simple recovery model scenarios
The Simple recovery model is not appropriate for databases in which the loss of recent
changes is unacceptable.
■ In the Bulk-Logged recovery model, the database engine minimally logs bulk oper-
ations such as SELECT INTO and BULK INSERT. In this recovery model, if a log
backup contains any bulk operation, you can restore the database to the end of
the log backup, not to a point in time. The Bulk-Logged recovery model is
intended to be used only during large bulk operations.
C0262271X.fm Page 70 Friday, April 29, 2005 7:29 PM
Lesson 3: Specifying a Recovery Model 71
How to Configure Recovery Models
You can see the recovery model specified for a given database on the Database Properties
page in SSMS or by querying the sys.databases catalog view, as this basic syntax shows:
SELECT name, recovery_model_desc FROM sys.databases
To configure the recovery model for a database, you can go to the Database Properties
page in SSMS or use the ALTER DATABASE statement.

In SSMS, you can change the recovery model by performing the following steps:
1. Expand the Databases node within Object Explorer in SSMS.
2. Right-click the database for which you want to set the recovery model and then
choose Properties. Select the Options page.
3. You can change the recovery mode from the Recovery model drop-down list, as
Figure 2-3 shows.
Figure 2-3 Changing the recovery model from SSMS
The basic syntax for configuring the recovery model using ALTER DATABASE is as
follows:
ALTER DATABASE <database _name>
SET RECOVERY FULL | SIMPLE | BULK_LOGGED
C0262271X.fm Page 71 Friday, April 29, 2005 7:29 PM
72 Chapter 2 Configuring SQL Server 2005
As noted earlier, Full recovery is the recommended model for a production database
because it provides the most recoverable configuration. If you import data periodi-
cally by using a bulk mechanism, you can temporarily change the recovery model for
your database to Bulk-Logged to get better bulk-load performance. Then, when the
import process ends, return your database to the Full recovery model.
PRACTICE Changing a Database’s Recovery Model
In this practice, you will change the database recovery model to Bulk-Logged to get
good performance for a bulk-logged operation and then revert to the Full recovery
model.
1. Set the database recovery model for the AdventureWorks database to Bulk-
Logged by executing the following ALTER DATABASE statement. (Before chang-
ing the recovery model, do a full backup of the database.)
Note that you should create the C:\Backup folder at Operating System level before
running this backup.
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AdventureWorks.Bak'
GO
Change the Recovery Model to Bulk Logged

ALTER DATABASE AdventureWorks
SET RECOVERY BULK_LOGGED
2. Type and then run the following ALTER DATABASE statement to change the
recovery model back to Full after performing the bulk-logged operations; per-
form another full database backup so that you have a backup of the data that
was just loaded:
ALTER DATABASE AdventureWorks
SET RECOVERY FULL
Perform a Full database backup
BACKUP DATABASE AdventureWorks TO DISK='C:\Backup\AdventureWorks.Bak'
GO
Lesson Summary
■ Recovery models let you control how the database engine logs operations and
which restore options are available for a particular database.
■ SQL Server provides three recovery models: Full, Simple, and Bulk-Logged.
■ The Full recovery model is the default and the recommended recovery model,
logging all operations and letting you recover to the point of failure.
C0262271X.fm Page 72 Friday, April 29, 2005 7:29 PM
Lesson 3: Specifying a Recovery Model 73
■ The Simple recovery model minimally logs most operations and doesn’t let you
back up or restore the transaction log.
■ The Bulk-Logged recovery model minimally logs bulk operations and is
intended for temporary use during large bulk operations.
■ You configure a database’s recovery model through the Database Properties win-
dow in SSMS or by using the ALTER DATABASE Transact-SQL statement.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review
them in electronic form.
NOTE Answers

Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which of the following sentences is true for recovery models?
A. In the Simple recovery model, most transactions are minimally logged.
B. In the Full recovery model, most transactions are minimally logged.
C. In the Bulk-Logged recovery model, all transactions are logged.
D. In the Simple recovery model, all transactions are logged.
2. Which of the following methods let you change the database recovery model?
(Choose all that apply.)
A. The sp_configure stored procedure
B. Database properties in SSMS
C. ALTER DATABASE
D. CREATE DATABASE
3. Which of the following restore operations are NOT allowed in the Simple recov-
ery model? (Choose all that apply.)
A. Point-in-Time Restore
B. Differential
C. Full
D. Page Restore
C0262271X.fm Page 73 Friday, April 29, 2005 7:29 PM
74 Chapter 2 Configuring SQL Server 2005
Lesson 4: Configuring Server Security Principals
SQL Server 2005 provides a strong security model that helps you prevent unautho-
rized access to your important data resources. This model is based on permissions
that you give principals—the individuals, groups, and processes that can request SQL
Server resources.
SQL Server 2005 authenticates the permissions of all user connections, so all user
connections must specify authentication mode and credentials. You can choose
between two authentication modes—Windows authentication and Mixed Mode authen-
tication—that control how application users connect to SQL Server. And you can create

two types of SQL Server logins—Windows logins and SQL Server logins—that let you
manage access to the SQL Server instance. To help manage the logins of principals
that have administrative privileges to SQL Server, you can arrange these logins in fixed
server roles. Authentication mode and logins are the first security level for SQL Server,
so you should take care to configure the most secure option for your environment.
After this lesson, you will be able to:
■ Choose between authentication modes.
■ Manage SQL Server logins.
■ Manage fixed server roles.
Estimated lesson time: 10 minutes
Choosing Between Authentication Modes
SQL Server 2005 provides two modes for authenticating access to database resources:
Windows authentication and Mixed Mode authentication.
■ Windows authentication When you configure SQL Server 2005 to use Windows
authentication, only authenticated Windows users can gain access to the SQL
Server instance. You need to add a Windows login for each Windows user or
group that needs access to a SQL Server instance. This is the default and recom-
mended authentication mode because you can take advantage of all the central-
ized security policies of your Active Directory domain.
■ Mixed Mode authentication With Mixed Mode authentication, both Windows
logins and SQL Server logins (neither of which are mapped to an operating sys-
tem user) can access the SQL Server instance. You use Mixed Mode authentica-
tion when you need to provide access to non-Windows users—for example, when
users of another client operating system need access to SQL Server.
C0262271X.fm Page 74 Friday, April 29, 2005 7:29 PM
Lesson 4: Configuring Server Security Principals 75
You can change the authentication mode by using Server Properties in SSMS by taking
the following steps:
1. In SSMS, right-click on your server and choose Properties.
2. Select the Security page.

3. Below Server Authentication, select the authentication mode you want to use on
your server. You can select either the Windows authentication mode or the SQL
Server And Windows authentication mode.
4. Click OK to save your changes.
5. Click OK to close the message box stating that your changes will not take effect
until you restart SQL Server.
6. To restart your server, right-click on your server in Object Explorer and choose
Restart.
Quick Check
■ Which authentication mode is the default and recommended mode for
security principals?
Quick Check Answer
■ Windows authentication
How to Configure SQL Server Logins
Logins are the server principals that give users access to SQL Server. You can create
SQL Server logins graphically in SSMS or by using the CREATE LOGIN statement.
The basic CREATE LOGIN syntax to create a Windows login is
CREATE LOGIN [Domain\User ] FROM WINDOWS
The syntax to create a SQL Server login is
CREATE LOGIN login_name WITH PASSWORD='password'
For SQL Server logins, you can specify the following options when creating the login:
■ MUST_CHANGE The login should change the password at the next login.
■ CHECK_EXPIRATION SQL Server will check the Windows expiration policy
for the SQL Server login.
■ CHECK_POLICY SQL Server will apply the local Windows password policy on
SQL Server logins.
C0262271X.fm Page 75 Friday, April 29, 2005 7:29 PM
76 Chapter 2 Configuring SQL Server 2005
BEST PRACTICES Password policies
To get a secure SQL Server environment, you should use the options to check the Windows expi-

ration policy for SQL Server logins and apply the local Windows password policy on them.
In the following example, you create a SQL Server login and force checking of pass-
word expiration and password policy:
CREATE LOGIN secureSQL WITH PASSWORD='Ty%6tsfs$g23', CHECK_EXPIRATION=ON, CHECK_POLICY =ON
If you need to change any login property, you can use the ALTER LOGIN statement.
The following example shows you how to change the password for a SQL Server login:
ALTER LOGIN login_name WITH PASSWORD='password'
You can disable a login by executing the following:
ALTER LOGIN login_name DISABLE
When you need to remove a login, you can use the DROP LOGIN statement:
DROP LOGIN login_name
Or use the following to drop a Windows login:
DROP LOGIN [Domain\User]
To get SQL Server login information such as state or login options, you can query the
sys.sql_logins catalog view.
CAUTION Removing logins
You cannot drop a login that owns any securable, server-level object, or SQL Server Agent job. You
should disable logins before dropping them, and drop logins only when you are sure the action will
not affect your environment.
In addition, if the login is mapped to a database user and you drop the login, SQL Server does not
automatically remove the user, resulting in an orphaned user.
DBAs commonly need to manage exceptions when providing access to a Windows
group. For example, you might need to provide SQL Server access to all the members
of a certain Windows group except for one member. To accomplish this task, you
should create a Windows login for the Windows group and then deny access to the
user who shouldn’t receive access. The following example shows the basic syntax for
accomplishing these steps:
CREATE LOGIN [domain_name\group_name] FROM WINDOWS
DENY CONNECT SQL TO [domain_name\user_name]
C0262271X.fm Page 76 Friday, April 29, 2005 7:29 PM

Lesson 4: Configuring Server Security Principals 77
NOTE Backward compatibility
You can use SQL Server 2000 stored procedures, such as sp_addlogin, sp_droplogin, and so on, to
manage logins. But remember that these stored procedures are in SQL Server 2005 only for back-
ward-compatibility purposes.
Managing Fixed Server Roles
SQL Server provides a set of fixed server roles, such as sysadmin and securityadmin,
which you can use to assign and manage administrative privileges to logins by adding
logins as members of these roles. Table 2-2 describes the fixed server roles for SQL
Server 2005.
To obtain information about logins for a fixed server role, you can query the
sys.server_role_members catalog view, which returns a row for each member of the
server role.
The basic syntax for adding a login to a fixed server role is
EXECUTE sp_addsrvrolemember login_name, fixed_server_role
You can use the sp_dropsrvrolemember stored procedure to remove the login from the
fixed server role.
Table 2-2 SQL Server’s Fixed Server Roles
Fixed Server Role Members Can
sysadmin Perform any activity in SQL Server. The permissions of this
role comprise the permissions of all other fixed server roles.
serveradmin Configure server-wide settings.
setupadmin Add and remove linked servers and execute some system
stored procedures, such as sp_serveroption.
securityadmin Manage server logins.
processadmin Manage processes running in an instance of SQL Server.
dbcreator Create and alter databases.
diskadmin Manage disk files.
bulkadmin Execute the BULK INSERT statement.
C0262271X.fm Page 77 Friday, April 29, 2005 7:29 PM

78 Chapter 2 Configuring SQL Server 2005
Alternatively, you can use SSMS to add and remove logins from fixed server roles.
You can accomplish these tasks by displaying the properties for either a login or a
server role.
MORE INFO Fixed server roles properties
For more information about fixed server roles and their properties, see the “Server-Level Roles”
topic in SQL Server 2005 Books Online.
PRACTICE Selecting an Authentication Mode and Creating a Login
In these practices, you will change your server’s authentication mode to Mixed Mode
and create a SQL Server login. You will enforce the password policy and expiration
policy for that login and add the login to the sysadmin fixed server role.
 Practice 1: Change Authentication Mode
In this practice, you will change authentication mode to Mixed Mode.
1. In SSMS, right-click your server and choose Properties.
2. Select the Security page. Below Server Authentication, select SQL Server And
Windows Authentication mode. Click OK. A warning message appears inform-
ing you that this change will take effect only after you restart SQL Server.
3. Right-click your server and choose Restart so the change will take effect.
 Practice 2: Add a SQL Server Login
In this practice, you will add a new SQL Server login and enforce the expiration and
check policy restrictions. Then you will add the login to the sysadmin fixed server role.
1. Expand the Security node, right-click Logins, and then choose New Login. The
New Login dialog box appears.
2. In the Login Name text box, type sqlLogin.
3. Select the SQL Server Authentication option; in the Password and Confirm Pass-
word text boxes, type the password Pa$$w0rd.
4. Clear the User Must Change Password At Next Login check box.
5. To add the login to the sysadmin fixed server role, select the Server Roles page.
Select the Sysadmin check box and click OK.
C0262271X.fm Page 78 Friday, April 29, 2005 7:29 PM

Lesson 4: Configuring Server Security Principals 79
Lesson Summary
■ Server principals provide a mechanism for controlling how SQL Server authen-
ticates user access to database resources.
■ SQL Server provides two authentication modes: Windows authentication—the
default and recommended mode—and Mixed Mode authentication, which you
use only if you need to give access to non-Windows users.
■ Each user connection should specify a valid login so that the database engine
can authenticate the connection and check the permissions.
■ To help manage administrative privileges to SQL Server, you can assign logins to
fixed server roles, which define ready-made permissions for members of each
role.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review
them in electronic form.
NOTE Answers
Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which of the following are valid SQL Server principals? (Choose all that apply.)
A. Database users
B. Fixed server roles
C. Windows logins
D. SQL Server logins
C0262271X.fm Page 79 Friday, April 29, 2005 7:29 PM
80 Chapter 2 Configuring SQL Server 2005
2. Which of the following sentences are true regarding authentication modes?
(Choose all that apply.)
A. Windows authentication is the preferred authentication mode.
B. Mixed Mode authentication does not let you apply password policies.

C. Windows authentication is the default authentication mode.
D. Mixed Mode authentication is the default authentication mode.
3. Which of the following statements let you create a SQL Server login called Peter?
(Choose all that apply.)
A. CREATE LOGIN Peter FROM SQL
B. CREATE LOGIN Peter WITH PASSWORD=‘Pa$$w0rd’
C. EXEC sp_addlogin ‘Peter’,‘Pa$$w0rd’
D. EXEC sp_grantlogin ‘Peter’,‘Pa$$w0rd’
C0262271X.fm Page 80 Friday, April 29, 2005 7:29 PM
Lesson 5: Configuring Database Securables 81
Lesson 5: Configuring Database Securables
Although server security principals are the entities requesting access to database
resources, server securables are the entities that you allow or disallow principals to
access. At the highest securable level are servers and databases, but you can also set per-
missions at a more granular level. This lesson covers securables at the database level.
After you configure the authentication mode and create logins for the principals, you
need to give them appropriate database access. You do this by mapping each database
login needing access to the database to a database user. For faster and easier admin-
istration, you can add database users as members of database roles.
After this lesson, you will be able to:
■ Manage database users.
■ Manage database roles.
■ Manage schemas.
Estimated lesson time: 20 minutes
Managing Database Users
To give logins access to a database, you need to create a database user for each login
that needs access to the database. You should create the user in the database in which
the user needs access. The basic syntax to create a database user is
CREATE USER user_name FOR LOGIN login_name
If you do not specify a login name, SQL Server will try to create a user mapped to a

login with the same name.
You can use the ALTER USER statement to modify user properties and the DROP
USER statement to remove database users.
You can also use SSMS to create and manage database users. You can either manage data-
base users from Logins below the Security node or Users below each Database node.
When a login that doesn’t have a database user mapped to it tries to access a database,
SQL Server looks for the Guest database user. SQL Server creates a Guest user in each
database. By default, the Guest user is not permitted to connect to the database. You
can allow guest connections by activating the Guest user, as follows:
GRANT CONNECT TO Guest
C0262271X.fm Page 81 Friday, April 29, 2005 7:29 PM
82 Chapter 2 Configuring SQL Server 2005
You can revoke guest access by executing the following:
REVOKE CONNECT TO Guest
Managing Orphaned Users
Orphaned users are database users that are not mapped to a login in the current SQL
Server instance. In SQL Server 2005, a user can become orphaned when you drop its
mapped login. To obtain information about orphaned users, you can execute the fol-
lowing command:
USE AdventureWorks;
GO
EXECUTE sp_change_users_login @Action='Report';
CAUTION Removing database users
The database engine doesn’t let you remove database users if they own a schema that contains
objects. You need to transfer the schema to another user or role before removing the database
user.
Managing Database Roles
If you have many database users, the process of creating them, modifying them,
removing them, and ensuring that they have correct permissions can become tedious
and time-consuming. To help you manage these tasks, each user database provides a

set of fixed database roles that you can use to group like database users. Table 2-3 lists
these fixed database roles.
Table 2-3 SQL Server Fixed Database Roles
Fixed Database Role Database-Level Permission
db_accessadmin Granted: ALTER ANY USER, CREATE SCHEMA
db_accessadmin Granted with GRANT option: CONNECT
db_backupoperator Granted: BACKUP DATABASE, BACKUP LOG, CHECK-
POINT
db_datareader Granted: SELECT
db_datawriter Granted: DELETE, INSERT, UPDATE
C0262271X.fm Page 82 Friday, April 29, 2005 7:29 PM
Lesson 5: Configuring Database Securables 83
NOTE Managing database role members
Members of the db_owner and db_securityadmin roles can manage members of fixed database roles,
but only members of the db_owner role can add members to the db_owner role.
You can also create your own database roles to group database users who have the
same access needs and assign permissions on a per-group basis instead of assigning
permissions user by user. For example, you can group users who are members of the
Accounting department into a database role called Accounting so that you can assign
permissions to only that database role and have the permissions applied to all mem-
bers of that role.
The basic syntax for creating a database role is
CREATE ROLE role_name
db_ddladmin Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYM-
METRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY
CONTRACT, ALTER ANY DATABASE DDL TRIGGER,
ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER
ANY DATASPACE, ALTER ANY FULLTEXT CATALOG,
ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SER-
VICE BINDING, ALTER ANY ROUTE, ALTER ANY

SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMET-
RIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE
DEFAULT, CREATE FUNCTION, CREATE PROCEDURE,
CREATE QUEUE, CREATE RULE, CREATE SYNONYM,
CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE
XML SCHEMA COLLECTION, REFERENCES
db_denydatareader Denied: SELECT
db_denydatawriter Denied: DELETE, INSERT, UPDATE
db_owner Granted with GRANT option: CONTROL
db_securityadmin Granted: ALTER ANY APPLICATION ROLE, ALTER ANY
ROLE, CREATE SCHEMA, VIEW DEFINITION
Table 2-3 SQL Server Fixed Database Roles
Fixed Database Role Database-Level Permission
C0262271X.fm Page 83 Friday, April 29, 2005 7:29 PM
84 Chapter 2 Configuring SQL Server 2005
You can modify role properties by using the ALTER ROLE statement and remove data-
base roles by using the DROP ROLE statement. You can also manage database roles by
using SSMS from the Security node below each database.
To add a database user to a role, you use the sp_addrolemember stored procedure,
which has the following basic syntax:
EXECUTE sp_addrolemember role_name, user_name
Alternatively, you can add a database user to a role via SSMS by modifying the data-
base user’s properties or the role’s properties.
You can nest database roles, so you can add database roles into other roles. For exam-
ple, suppose that you want to group managers in the Accounting department into a
database role called AccountingMgr. You could grant that role the permissions of the
entire Accounting role by nesting Accounting within AccountingMgr and then just grant-
ing the extra manager permissions to the AccountingMgr role. To obtain information
about database role members, you can query the sys.database_role_members catalog
view, which returns one row for each member of the database role.

Quick Check
■ True or False: Database roles are all fixed, giving you a predefined set of
permissions that you can grant to a group of like database users.
Quick Check Answer
■ False. Although SQL Server provides a set of fixed database roles, you can
also create your own roles.
Managing Schemas
SQL Server 2005 implements the ANSI concept of schemas, which are collections of
database objects—such as tables, views, stored procedures, and triggers—that form a
single namespace. The main benefit of schemas in SQL Server 2005 is that schemas
and users are now separate entities. User name is no longer part of object name, as it
was in previous versions of SQL Server, so you can remove users or change user
names without having to make application changes. Each schema is owned by a user
or role, but if you need to drop a user or role, you just transfer the schema ownership
from the user or role you’re dropping to another new user or role.
C0262271X.fm Page 84 Friday, April 29, 2005 7:29 PM
Lesson 5: Configuring Database Securables 85
The basic syntax to create a schema is
CREATE SCHEMA schema_name AUTHORIZATION owner
To modify a schema, you can use the ALTER SCHEMA statement; to remove a schema,
you can use the DROP SCHEMA statement. You can also accomplish these tasks from
SSMS. To retrieve information about schemas, you can query the sys.schemas catalog
view.
In addition, you can assign a default schema for each database user. This default
schema is used when the user does not specify the schema name when accessing an
object. For instance, if user Peter has a default schema of HumanResources and wants
to access the Employee table without specifying a schema, he can just specify
Employee instead of having to specify HumanResources.Employee.
You assign a default schema by using the CREATE USER or ALTER USER statement.
You also can assign a default schema through SSMS in the user’s properties.

PRACTICE Configuring Server Securables
In this practice, you will configure server securables for the AdventureWorks database.
You will create a login and database user for Peter. Peter needs access to the Human-
Resources schema objects in AdventureWorks.
1. Use the following CREATE LOGIN statement to create a SQL Server login and
database user named Peter that has access to the AdventureWorks database:
CREATE LOGIN Peter WITH PASSWORD='Pa$$w0rd'
GO
USE AdventureWorks
GO
CREATE USER Peter FROM LOGIN Peter
2. Grant Peter SELECT permission to HumanResources database objects by coding
the following statement (note the :: syntax to specify a schema name):
GRANT SELECT ON SCHEMA::[HumanResources] TO [Peter]
3. Click New Query. Right-click the query area and choose Connection | Change
Connection. Connect using the SQL login Peter with a password of Pa$$w0rd.
4. Execute the following query to test SQL Server login Peter’s access:
USE AdventureWorks
GO
SELECT * FROM Employee
C0262271X.fm Page 85 Friday, April 29, 2005 7:29 PM
86 Chapter 2 Configuring SQL Server 2005
5. Notice that you get an Invalid Object error message, meaning that login Peter
doesn’t have the correct permissions to the Employee table. You need to solve
this problem by running the following ALTER USER statement to assign Human-
Resources as the default schema for Peter so that he can select the Employee table
directly without having to use the HumanResources schema name to qualify the
table name:
ALTER USER Peter WITH DEFAULT_SCHEMA=HumanResources
6. Run the query from step 4 again. You should get a valid result set now.

Lesson Summary
■ Database users, roles, and schemas give you the tools you need to secure data-
base objects.
■ Each login is mapped to a database user for each database that the login needs
access to.
■ Database roles let you group users with the same permissions and same data-
base access needs for easy management.
■ Schemas, a new concept in SQL Server 2005, separate schemas (a collection of
database objects that form one namespace) from users so that you can now man-
age these entities individually.
Lesson Review
The following questions are intended to reinforce key information presented in this
lesson. The questions are also available on the companion CD if you prefer to review
them in electronic form.
NOTE Answers
Answers to these questions and explanations of why each answer choice is right or wrong are
located in the “Answers” section at the end of the book.
1. Which of the following sentences is true for database schemas?
A. Database schemas define the database catalog.
B. Database schemas group database objects.
C. Database schemas group databases.
D. Database schemas define the table catalog.
C0262271X.fm Page 86 Friday, April 29, 2005 7:29 PM