To cancel SQL Server database logon user permissions by using SQL statements

server| Data | database | user RIGHTS | statement REVOKE
Deletes a permission that was previously granted or denied on a user in the current database.
Grammar
Statement permissions:

REVOKE {ALL | statement [,... n]} from security_account [,... N]

Object permissions:

REVOKE [GRANT OPTION for] {all [privileges] | permission [,... n]} {[(column [,... N]]] on {TA ble | View} | On {table | view} [(column [,... n])] | on {stored_procedure | extended_procedure} | on {user_defined_function}} {to | from} security_account [,... n] [CASCADE] [as {group | role}]
Parameters
All

Specifies that all applicable permissions will be deleted. For statement permissions, all is available only to members of the sysadmin fixed server role. For object permissions, all can be used by members of the sysadmin fixed server role, Db_owne fixed database role members, and database object owners.

Statement

Is the authorization statement whose permissions you want to delete. The statement list can include:
CREATE databasecreate defaultcreate functioncreate procedurecreate rulecreate tablecreate VIEWBACKUP LOG
N

is a placeholder that represents items that can be duplicated in a comma-delimited list.

From

Specify a list of security accounts.

Security_account

is the security account in the current database for which permissions will be removed. The security account can be:
Microsoft®sql Server™ users. SQL Server role. Microsoft Windows NT® users. Windows NT Group.
You cannot revoke permissions for a system role (such as sysadmin). When you revoke permissions from SQL Server or a Windows NT user account, the specified security_account is the only account that is affected by permissions. If you revoke permissions from a SQL Server role or a Windows NT group, these permissions affect users in the current database who are members of a group or role, unless the user has been explicitly granted or denied permissions.

REVOKE can be used in two special security accounts. The permissions that are revoked on the public role apply to all users within the database. The permissions revoked on the guest user will be used by all users in the database who do not have a user account.

When you abolish permissions for a Windows NT local group or global group, specify the domain name or computer name that is defined on the group, followed by a backslash and then the group name, such as London\JoeB. However, to abolish the Windows NT built-in Local group permissions, specify BUILTIN instead of the domain name or computer name, such as BUILTIN\Users.

GRANT OPTION for

Specifies the with GRANT OPTION permission to be removed. Use the grant option for keyword in REVOKE to eliminate the effect of the WITH GRANT option setting specified in the GRANT statement. The user still has this permission, but the permission cannot be granted to another user.

If the permission to be revoked was not previously granted through the WITH GRANT option setting, the grant option for (if specified) is ignored and the permissions are routinely revoked.

Specifies the CASCADE and grant OPTION for clause if the permission to be revoked was previously granted through the WITH GRANT option setting; otherwise, an error is returned.

Privileges

is an optional keyword that can be included in statements that conform to the SQL-92 standard.

Permission

is the object permission to be revoked. When you revoke a table or an attempted permission, the permission list can include one or more of these statements: SELECT, INSERT, DELETE, or UPDATE.

Object permissions that are revoked on a table can also include REFERENCES, and object permissions that are revoked on a stored procedure or extended stored procedure can be EXECUTE. When you revoke permissions on a column, the list of permissions can include SELECT or UPDATE.

Column

is the name of the column in the current database to be removed.

Table

is the name of the table in the current database to which permissions are being removed.

View

is the name of the view in the current database for which permissions are to be removed.

Stored_procedure

is the name of the stored procedure in the current database for which permissions are being removed.

Extended_procedure

is the name of the extended stored procedure for the permission to be removed.

User_defined_function

is the name of the user-defined function to be removed.

To

Specify a list of security accounts.

CASCADE

Specifies that any additional security accounts authorized by Security_account will also be deleted when the permissions from the security_account are deleted. Use CASCADE when revoking permissions that can be granted.

Specifies the CASCADE and grant option for clauses if the permission to be revoked was originally granted to security_account through the WITH GRANT option setting; otherwise, an error is returned. Specifying CASCADE and GRANT OPTION for clauses will only revoke permissions granted to security_account through the WITH GRANT option and other security accounts authorized by Security_account.

As {Group | role}

Specifies an optional name for the security account in the current database (executing the REVOKE statement under its authorization). If you give an object permission to a group or role, but you need to revoke the object permission on another user, you can use as. Because a REVOKE statement can only be executed by a user and cannot be performed by a group or role, a specific member of a group or role abolishes grant object permissions under the authority of that group or role.
Comments
REVOKE only applies to permissions within the current database.

The revoked permission removes the granted or denied permission only on the level (user, Group, or role) of the revoked permission. For example, the Andrew user account is explicitly granted permission to view the authors table, which is only a member of the employees role. If the employees role is abolished to view access to the authors table, the account can still view the table because it has been explicitly granted permission to view the table. Andrew would not be able to view the authors table only if it had also abolished the granting of Andrew's permission. If Andrew is never explicitly granted permission to view authors, then revoking the permissions of the employees role will also prevent Andrew from viewing the table.



Describes REVOKE to remove previously granted or denied permissions. You may need to make changes to scripts in Microsoft®sql server™6.5 or earlier versions that use REVOKE to use the DENY maintenance behavior.



If the user activates an application role, REVOKE will not affect the user's access to the object using the application role. Although you can revoke the user's access to a particular object within the current database, the user will also have access to the application if the application has permission to access it.

Use Sp_helprotect to report the permissions of a database object or user.
Permissions
REVOKE permissions are granted to members of the sysadmin fixed server role, db_owner and db_securityadmin fixed database roles, and database object owners by default.
Example A. Revoke statement permissions granted to a user account
The following example abolishes the CREATE TABLE permission that has been granted to the user Joe and Corporate\BobJ. It removes permission to allow Joe and corporate\bobj to create a table. However, if you have given the CREATE TABLE permission to any role that contains Joe and Corporate\BobJ members, Joe and Corporate\BobJ can still create tables.

REVOKE CREATE TABLE from Joe, [Corporate\BobJ]
B. Revoking multiple permissions granted to multiple user accounts
The following example abolishes multiple statement permissions that are granted to multiple users.

REVOKE Create TABLE, create Defaultfrom Mary, John
C. Abolition of denied permission
User Mary is a member of the Budget role and has given the role a SELECT permission to the Budget_data table. A DENY statement was used against Mary to prevent Mary from accessing the Budget_data table by granting permission to the Budget role.

The following example deletes the permission denied to Mary and allows Mary to use a SELECT statement on the table by using the SELECT permission that applies to the Budget role.

REVOKE SELECT on Budget_data to Mary