Resolving Permission Conflicts
The permissions granted to a group or role are inherited by members of that group or role. Although a user may have permission granted or revoked at one level, conflicting permissions at a higher level (for example, due to role membership) can prevent or allow a user access to an object.
Deny
A denied permission always takes precedence. Denied permission at any level (user, group, or role) denies the permission on the object regardless of existing granted or revoked permissions for that user. For example, if user John, who as a member of the sales role is granted SELECT permissions on the customer table, is explicitly denied SELECT permissions on the customer table, he can no longer access it. Similarly, if the sales role is denied access to customer, but John is granted access, he is denied access.
Note Microsoft® SQL Server™ always processes denied permissions first. If you deny permissions to public, you prevent anyone from accessing an object, including the issuer of the DENY statement.
Revoke
A revoked permission removes only the granted or denied permission at the level revoked (user, group, or role). The same permission granted or denied at another level such as a group or role containing the user, group, or role still applies. For example, if the sales role is granted SELECT permissions on the customer table, and John (a member of sales) is explicitly revoked SELECT permissions on the customer table, he still can access the table because of his membership in the sales role. To prevent John from accessing the customer table, do one of the following:
- Revoke permission (assuming no other permissions have been granted elsewhere).
- Deny permission to the sales role (preventing all members of sales from accessing the table).
- Explicitly deny John SELECT permissions on customer.
Grant
A granted permission removes the denied or revoked permission at the level granted (user, group, or role). The same permission denied at another level such as group or role containing the user still applies. However, although the same permission revoked at another level still applies, it does not prevent the user from accessing the object. For example, if John is already explicitly denied access to customer, has his access to sales, revoked, and then is explicitly granted access to customer, he now can access customer because the deny is removed. The revoke permission for sales joined with the granted permission for John gives John a granted permission overall.
Therefore, a user receives the union of all the permissions granted, denied, or revoked on an object, with any denied permissions taking precedence over the same permissions granted or revoked at another level.
The following diagram shows how the three permission management activities affect the state of a permission for a user account.
Database Access vs. Object Access
As an example of a permission conflict, a Microsoft Windows NT® 4.0 user LONDON\joe belongs to the LONDON\clerks and LONDON\secretaries Windows NT 4.0 groups. LONDON\joe can log in to an instance of SQL Server because the LONDON\clerks group has been granted permissions to connect to an instance of SQL Server. Additionally, LONDON\joe can access the secrets database because the LONDON\secretaries group has been granted permissions to access the database.
Note At this point there is no specific entry in the SQL Server system tables, sysusers and sysxlogins, for LONDON\joe. These system tables contain only entries for the LONDON\clerks and LONDON\secretaries groups.
LONDON\joe creates a table, joetable, in the secrets database. At this point, a new entry is created in the sysusers table for LONDON\joe specifying him as the object owner but not granting him database access. If LONDON\joe is dropped from the LONDON\secretaries group, he can no longer access the secrets database, although he owns an object, joetable, in the database.