Denying Permissions

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

• Removes permission granted previously to the user, group, or role.
  
  
• Deactivates permission inherited from another role(s).
  
  
• Ensures 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 you want to prevent from seeing the CorporateSecrets table.

Create a role for each department in the company and add all employees to their department role. 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 each inherits permission from the Corporate role through his 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, while the rest of the employees in the company are not affected.

You also can deny permissions to an individual user. In the previous example, a nonemployee may have a Windows NT 4.0 or Windows 2000 account while working on a short-term project in the database. You can deny the permissions to see CorporateSecrets to his 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)

Transact-SQL