INF: How Grant and Revoke work in SQL Server 6.5.

• Microsoft SQL Server, version 6.5

SUMMARY

In SQL Server version 6.5, the implementation of permissions has changed compared to previous versions. This article describes the SQL Server 6.5 behavior.

MORE INFORMATION

The Sysprotects table maintains information about user and group permissions. A change in SQL Server 6.5 from the previous version is how it writes to the Sysprotects table, which in turn affects what permission is assigned to a user or to a group. SQL Server determines what to write to the Sysprotects table based on the current content of the table.

SQL Server evaluates whether a row exists in the Sysprotects table for the specific permission, action, object, and user or group. If a row exists in Sysprotects relating to the specific permission, SQL Server performs one of the following steps:

• If a row exists, and the current GRANT or REVOKE statement duplicates the existing row, nothing is entered in Sysprotects.
• If a row exists with GRANT permission and the current REVOKE statement is the converse of the previously entered row, the previously entered row is deleted from Sysprotects.
• If a row exists with REVOKE permission, and the current GRANT statement is the converse of the previously entered row, the previously entered row is deleted and a new row with GRANT permission is entered in Sysprotects.

• If a GRANT permission is issued, a new row with GRANT permission is entered in Sysprotects.
• A user is subordinate to a group, and a group is subordinate to a public. If a REVOKE permission is issued for a user or group and the user's group or public group has already been granted permission (has an entry with GRANT permission in Sysprotects), a new row with REVOKE permission is entered in Sysprotects.
• If a REVOKE permission is issued for a user or group and there is no entry with GRANT permission in Sysprotects for user's group or public group, no new row is entered in Sysprotects.

The following example displays the behavior mentioned above. In the example, user1 is a user and group1 is a group which contains user1.

Step 1: Grant permission to a user. This will enter a row with GRANT in

        Sysprotects.

Result:

Owner Object       Grantee Grantor ProtectType Action Column

dbo   authors_test  user1   dbo     Grant      Select (All+New)

        Sysprotects.

Result:

Owner Object       Grantee Grantor ProtectType Action Column

dbo   authors_test  user1   dbo     Grant      Select (All+New)
dbo   authors_test  group1  dbo     Grant      Select (All+New)

        assigned GRANT to group1 and only the one row with GRANT to user1
        is left in Sysprotects.

Result:

Owner Object       Grantee Grantor ProtectType Action Column

dbo   authors_test  user1   dbo     Grant      Select (All+New)

        any entry in Sysprotects.

Result:

Owner Object       Grantee Grantor ProtectType Action Column

dbo   authors_test  user1   dbo     Grant      Select (All+New)

        two more entries in Sysprotects.

Result:

Owner Object       Grantee Grantor ProtectType Action Column

dbo   authors_test  user1   dbo     Grant      Select (All+New)
dbo   authors_test  group1  dbo     Revoke     Select (All+New)
dbo   authors_test  public  dbo     Grant      Select (All+New)

        permission from group1 and adds a new entry for GRANT permission to
        group1.

Result:

Owner Object       Grantee Grantor ProtectType Action Column

dbo   authors_test  user1   dbo     Grant      Select (All+New)
dbo   authors_test  group1  dbo     Grant      Select (All+New)
dbo   authors_test  public  dbo     Grant      Select (All+New)