sp_dropuser

Drops a user from the current database.

Syntax

sp_dropuser name_in_db

Parameters

Examples

Usage

There are additional considerations when using sp_dropuser:
  • sp_dropuser drops a user from the current database by deleting the user’s row from sysusers.

  • You cannot drop:
    • A user who owns objects in the database.

    • A user who has granted permissions to other users.

    • The database owner from a database.

  • If other users are aliased to the user being dropped, their aliases are also dropped. They no longer have access to the database.

  • You cannot drop a user from a database if the user owns a stored procedure that is bound to an execution class in that database. See sp_bindexeclass.

  • sp_dropuser drops all key copies from sysencryptkeys for the specified user in the current database. sp_dropuser fails if the user owns an encryption key in any database. See the Encrypted Columns Users Guide.

See also grant, revoke, use in Reference Manual: Commands.

Permissions

The permission checks for sp_dropuser differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage any user privilege.

Disabled

With granular permissions disabled, you must be the datatype owner, a user with sa_role, or a user with sso_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addalias
sp_adduser
sp_bindexeclass
sp_droplogin