Microsoft® SQL Server® 2012 Bible

850

Part VI: Securing Your SQL Server

Unfortunately, the list of objects appears to be unsorted, or only partially sorted, and the

grid headers don’t re-sort the list of objects. This dialog also desperately needs a select all

functions and other features, such as those in Access’s permissions forms.

From the Role List

The third way to control object permissions is from the database role. To open the Database

Role Properties dialog, double-click a role in the list of roles, or select Properties from the

right-click context menu. You can use the Database Role Properties dialog to assign users or

other roles to the role, and to remove them from the role.

The Permissions button opens the Permissions dialog box for the role. This form operates

like the other permission forms except that it is organized from the role’s perspective.

A Sample Security Model

The simplest way to assign permissions when more granularity is needed is to create

user-defi ned roles and select effective permissions. Tables 33-2 and 33-3 list sample user-

defi ned roles and user permission settings of the user-defi ned database roles. Table 33-3

lists a few of the users and their roles.

TABLE 33-2 Sample User-Defi ned Role Permission Assignment

User-Defi ned

Role

Hierarchical Role

Structures

Primary Filegroup

Tables

Static Filegroup

Tables Other Permissions

DBTeam sysadmin server

role

---

DataEntry - - - Executes

permissions for

several stored

procedures that

read from and

update required

day-to-day tables

Admin db_owner data-

base fi xed role

---

Customer - Select

permissions

--

c33.indd 850c33.indd 850 7/31/2012 10:01:25 AM7/31/2012 10:01:25 AM

http://www.it-ebooks.info