Database users can perform activities that are granted to them. In this case, there is a corresponding entry in the sys.database_permissions catalog view (that is, the value of the state column is set to G for grant). A negative entry in the table prevents a user from performing activities. The entry D (deny) in the state column overrides a permission that was granted to a user explicitly or implicitly using a role to which the user belongs. Therefore, the user cannot perform this activity in any case. In the last case (value R), the user has no explicit privileges but can perform an activity if a role to which the user belongs has the appropriate permission.
To manage permissions for a user or role using Management Studio, expand the server and expand Databases. Right-click the database and click Properties. Choose the Permissions page and click the Search button. In the Database Properties dialog box, shown in Figure 12-6, you can select one or more object types (users and/or roles) to which you want to grant or deny permissions. To grant a permission, check the corresponding box in the Grant column and click OK. To deny a permission, check the corresponding box in the Deny column. (The With Grant column specifies that the user has the additional capability of granting the privilege to other users.) Blanks in these columns mean no permission.
To manage permissions for a single database object using SQL Server Management Studio, expand the server, expand Databases, expand the database, and then expand Tables, Views, or Synonyms, depending on the database object for which you want to manage permissions. Right-click the object, choose Properties, and select the Permissions page. (Figure 12-7 shows the Table Properties dialog box for the department table.) Click the Search button to open the Select Users or Roles dialog box. Click Object Types and select one or more object types (users, database roles, application roles). After that, click Browse and check all objects to which permissions should be granted. To grant a permission, check the corresponding box in the Grant column. To deny a permission, check the corresponding box in the Deny column.
