sp_revokedbaccess

Transact-SQL Reference

Transact-SQL Reference

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'

See Also

sp_changeobjectowner

sp_droprole

sp_grantdbaccess

System Stored Procedures