Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VIII.3.3. Who Can You Let Use Your Database?

The preceding section shows all the items that you can secure. The next question to answer is what kind of users can work with your database. In fact, some of these users aren't people; they're application programs and processes. Regardless of whether the entity accessing your database ingests food or electricity, SQL Server uses the term principal to describe them.

The three major classifications of principal, which in turn contain resources, are as follows:

1. Operating system-based principals

   Windows domain login

   Windows local

2. SQL Server-based principals

   SQL Server login

3. Database-based principals

   Database user

   Database role

   Application role

SQL Server also supports the pre-packaged permissions concept. These are known as roles, but you can think of them as a one-stop shop that allows you to grant permissions en masse. Table 3-1 lists all the fixed server-level roles along with their purposes. Table 3-2 lists the same for fixed database-level roles.

Table VIII.3-1. SQL Server Fixed Server Roles

Name	Permission Available
bulkadmin	Run the BULK INSERT command
dbcreator	Create, change, restore, or drop a database
diskadmin	Administer disk files
processadmin	End SQL Server processes
securityadmin	Set server and database-level permissions; set password
serveradmin	Shut down the server; modify server configuration values
setupadmin	Manage linked servers; run system stored procedures
sysadmin	Perform any administrative task on the server

Table VIII.3-2. SQL Server Fixed ...