sp_revokedbaccess
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 sysadmin fixed server role, and the db_accessadmin and 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'