sp_revokedbaccess (T-SQL)

Removes a security account from the current database.

Syntax

sp_revokedbaccess [@name_in_db =] 'name'

Arguments

[@name_in_db =] 'name'

Is the name of the account to be removed. name is sysname with no default. name can be the name of a Microsoft® SQL Server™ user, or Microsoft Windows NT® user or group, and must exist in the current database. When specifying a Windows NT user or group, specify the name the Windows NT user or group is known by in the database (added using sp_grantdbaccess).

Return Code Values

0 (success) or 1 (failure)

Remarks

When the account is removed, the permissions and aliases that depend on the account are automatically removed.

You can only remove accounts in the current database using sp_revokedbaccess. To add an account in the database, use sp_grantdbaccess. To remove a SQL Server role, use sp_droprole. When removing an account that owns objects in the current database, you must either remove the object, or change the owner of the object using sp_changeobjectowner, before executing sp_revokedbaccess.

The sp_revokedbaccess stored procedure cannot remove:

• The public role, or dbo or INFORMATION_SCHEMA users.
• The fixed roles in the database.
• The guest user account in the master and tempdb databases.
• A Windows NT user from a Windows NT group.

sp_revokedbaccess cannot be executed from within a user-defined transaction.

Permissions

Only members of the db_accessadmin or db_owner fixed database roles can execute sp_revokedbaccess.

Examples

This example removes the account Corporate\GeorgeW from the current database.

EXEC sp_revokedbaccess 'Corporate\GeorgeW'

See Also