sp_dropuser

Syntax

sp_dropuser name_in_db

name_in_db – is the user’s name in the current database’s sysusers table.

Example 1 – Drops the user “albert” from the current database. The user “albert” can no longer use the database:
```
sp_dropuser albert
```

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.

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

Setting	Description
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.

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

Information	Values
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