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.
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 is a member of the sales role, which 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.
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 can still access the table because of his membership in the sales role. To prevent John from accessing the customer table, either revoke (assuming no other permissions have been granted elsewhere) or deny permission to the sales role (preventing all members of sales from accessing the table), or explicitly deny John SELECT permissions on customer.
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, and sales is revoked access, and John is then explicitly granted access to customer, he can now 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.
As an example of a permission conflict, a Microsoft® Windows NT® user LONDON\joe belongs to the LONDON\clerks and LONDON\secretaries Windows NT groups. LONDON\joe can log in to Microsoft SQL Server™ because the LONDON\clerks group has been granted permissions to connect to 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 syslogins, 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.
Adding a Windows NT User or Group