Preventing Access by Denying Permissions

Microsoft® SQL Server™ allows Microsoft Windows NT® users, Windows NT groups, SQL Server users, and SQL Server database roles to be members of other roles. This results in a hierarchical security system allowing permissions to be applied through several levels of roles and members. But there may be times when you want to limit a certain user’s or role’s permissions. When you deny permissions on a user account, you:

• Remove permission granted previously to the user, group, or role.
• Deactivate permission inherited from another role(s).
• Ensure that a user, group, or role will not inherit permission from a higher level group or role in the future.

For example, you may need to provide all tenured employees in your company with access to several tables in a database, with the exception of a few new employees scattered throughout the organization who must be prevented from seeing the CorporateSecrets table.

You can create a role for each department in the company and add all employees to their department role. You can then create a company-wide Corporate role, to which you add each of the individual department roles and grant permissions to view the tables. At this point, every employee in the company can see all the tables because they inherit permission from the Corporate role through their department roles.

To selectively prevent employees from seeing CorporateSecrets, create a Nonsecure role, and add the individual employees who should not see the table. When you deny permission to view CorporateSecrets to Nonsecure, this access is removed from all members of Nonsecure, and the rest of the employees in the company are not affected.

You can also deny permissions to an individual user. In the previous example, a nonemployee may have a Windows NT account while working on a short-term project in the database. You can deny the permissions to see CorporateSecrets to their individual user account without creating a SQL Server database role for the purpose.

Note You can deny permissions to user accounts only in the current database, for objects in the current database.

To prevent access by denying permissions (on an object)

To deny statement permissions from users within a database

To deny permissions on multiple objects to a user, group, or role