News:

Build a stunning handcrafted website with IT Acumens

Main Menu

DENY

Started by sukishan, Aug 18, 2009, 08:01 PM

Previous topic - Next topic

sukishan

Creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.

Syntax
Statement permissions:

DENY { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]

Object permissions:

DENY
    { ALL [ PRIVILEGES ] | permission [ ,...n ] }
    {
        [ ( column [ ,...n ] ) ] ON { table | view }
        | ON { table | view } [ ( column [ ,...n ] ) ]
        | ON { stored_procedure | extended_procedure }
        | ON { user_defined_function }
    }
TO security_account [ ,...n ]
[ CASCADE ]

Arguments
ALL

Denies all applicable permissions. For statement permissions, ALL can be used only by members of the sysadmin and db_securityadmin roles. For object permissions, ALL can be used by members of the sysadmin, db_securityadmin, and db_owner roles, and by database object owners.

statement

Is the statement for which permission is denied. The statement list can include:

CREATE DATABASE


CREATE DEFAULT


CREATE FUNCTION


CREATE PROCEDURE


CREATE RULE


CREATE TABLE


CREATE VIEW


BACKUP DATABASE


BACKUP LOG
n

Is a placeholder indicating that the item can be repeated in a comma-separated list.

TO

Specifies the security account list.

security_account

Is the name of the security account in the current database affected by the denied permission. The security account can be a:

Microsoft® SQL Server™ user.


SQL Server role.


Microsoft Windows NT® user.


Windows NT group.
When a permission is denied from a SQL Server user or Windows NT user account, the specified security_account is the only account affected by the permission. If a permission is denied from a SQL Server role or a Windows NT group, the permission affects all users in the current database who are members of the group or role, regardless of the permissions that have been granted to the members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence.

Two special security accounts can be used with DENY. Permissions denied from the public role are applied to all users in the database. Permissions denied from the guest user are used by all users who do not have a user account in the database.

When denying permissions to a Windows NT local or global group, specify the domain or computer name the group is defined on, followed by a backslash, then the group name. However, to deny permissions to a Windows NT built-in local group, specify BUILTIN instead of the domain or computer name.

PRIVILEGES

Is an optional keyword that can be included for SQL-92 compliance.

permission

Is a denied object permission. When permissions are denied on a table or a view, the permission list can include one or more of these statements: SELECT, INSERT, DELETE, or UPDATE.

Object permissions denied on a table can also include REFERENCES, and object permissions denied on a stored procedure or extended stored procedure can include EXECUTE. When permissions are denied on columns, the permissions list can include SELECT or UPDATE.

column

Is the name of the column in the current database for which permissions are denied.

table

Is the name of the table in the current database for which permissions are denied.

view

Is the name of the view in the current database for which permissions are denied.

stored_procedure

Is the name of the stored procedure in the current database for which permissions are denied.

extended_procedure

Is the name of an extended stored procedure for which permissions are denied.

user_defined_function

Is the name of the user-defined function for which permissions are being denied.

CASCADE

Specifies that permissions are denied from security_account as well as any other security accounts granted permissions by security_account. Use CASCADE when denying a grantable permission. If CASCADE is not specified and the specified user is granted WITH GRANT OPTION permission, an error is returned.
A good beginning makes a good ending