Microsoft® SQL Server® 2012 Bible

849

Chapter 33: Authorizing Securables

33

■ (^) The manager role may have all worker rights plus additional rights to look up
tables.
■ (^) The administrator role may have all manager rights plus the right to perform other
database-administration tasks.
To accomplish this type of design, follow these steps:

1. Create the worker role and set its permissions.


2. Create the manager role and set its permissions. Add the manager role as a user to
   the worker role.


3. Create the admin role. Add the admin role as a user to the manager role.
   The advantage of this type of security organization is that a change in the lower level
   affects all upper levels. As a result, administration is required in one location, rather than
   dozens of locations.

Object Security and Management Studio

Because object permissions involve users, roles, and objects, they can be set from numerous

places within Management Studio. It’s almost a maze.

From the Object List

Follow these steps to modify an object’s permissions:

1. From an object node (tables, views, stored procedures, or user-defi ned functions) in
   the Object Browser, from the context menu select Properties to open the Properties
   dialog for that object type.


2. Click the Permissions page to open the Object Properties dialog.
   As with setting statement permissions on the Database Properties Security tab, you can
   select grant, with grant, or deny. The object list at the top of the dialog lists all the objects
   in the database. This list can be used to quickly switch to other objects without backing
   out of the form to the console and selecting a different object.

The Columns button at the bottom opens the Column Permissions dialog. Select the user, and

then click the button to set the columns permission for that user. Only select and update per-

missions can be set at the column level because inserts and deletes affect the entire row.

From the User List

From the list of database users in Management Studio, select a user and double-click, or

select Properties from the right-click context menu. The Database User Properties dialog is

used to assign users to roles.

Clicking the Properties button opens the properties of the selected role.

Clicking the Permissions button opens the Permissions tab of the Database User Properties

dialog. This dialog is similar to the Permissions tab of the Database Object Properties dialog.

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

http://www.it-ebooks.info