Permission Conflicts

Permissions can be administered for both groups and users. The permissions granted to a group are provided to the users who are members of that group. However, permissions are sensitive to the order in which they are granted or revoked. The examples that follow demonstrate how.

There are two basic ways to set up permissions in a database or on a database object. The most straightforward is to assign specific permissions to specific users. However, if most users are going to be granted most privileges, it's easier to assign all permissions to all users and then revoke specific permission from specific users. For example, the database owner of titles could:

1. Grant permissions on the titles table to all users by granting all object permissions to the public group.
2. Revoke update permissions from public for the royalty and advance columns on titles.
3. Revoke delete permissions on titles from the user mary, the group sales, and the user john.

In this example, most users have all permissions on titles except for update permissions on the royalty and advance columns. Users in the sales group, plus mary and john, are also denied delete permissions for the table.

As another example, if the user joe is in the opers group, the opers group is granted SELECT permission on the titles table, and joe's permission to select the advance column is revoked, then joe can select all the columns except advance, while the other users in his group can still select all the columns.

Granting or revoking permissions for a group will change any conflicting permissions that have been assigned to any member of a group. As an example, say the owner of the titles table has granted different permissions to various members of the sales group and then decides to standardize. This database owner could:

1. Revoke all permissions on titles from the sales group.
2. Grant select permission on the title, title_id, type, and pub_id columns of titles to the sales group.

In this example, all users in sales now have the same permissions for titles.

Granting or revoking permissions to the public group changes, for all users, any previously issued permissions that conflict with the new statement.

Permissions granted and revoked in different orders can create entirely different situations. For example, the following steps leave the user joe without any SELECT permission on titles (assuming Joe belongs to the public group):

1. Grant select permission on the title_id and title columns of the titles table to joe.
2. Revoke select permission on the titles table from the public group.

In contrast, suppose the same permissions are issued in the opposite order:

1. Revoke select permission on the titles table from the public group.
2. Grant select permission on the title_id and title columns of the titles table to joe.

In the second example, only joe has select permission for titles, and only on the title_id and title columns.

When you administer permissions for the public group, you are including yourself (if you are a member of the public group).

To grant or revoke permissions, use SQL Enterprise Manager or the GRANT and REVOKE statements. For information about managing permissions with SQL Enterprise Manager, see Chapter 9, Managing Security. For information about using the GRANT and REVOKE statements, see the Microsoft SQL Server Transact-SQL Reference.