Drops a user from the current database.
sp_dropuser name_in_db
is the user’s name in the current database’s sysusers table.
Drops the user “albert” from the current database. The user “albert” can no longer use the database:
sp_dropuser albert
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 Users Guide for Encrypted Columns.
The permission checks for sp_dropuser differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage any user privilege. |
Granular permissions 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:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_addalias, sp_adduser, sp_bindexeclass, sp_droplogin
