| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_dropuser (Transact-SQL) |
sp_dropuser removes a database user from the current database. |
markingmyname |
maghan |
randolphwest |
08/22/2024 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Removes a database user from the current database. sp_dropuser provides compatibility with earlier versions of [!INCLUDE ssNoVersion].
Important
[!INCLUDE ssNoteDepFutureAvoid] Use DROP USER instead.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_dropuser [ @name_in_db = ] N'name_in_db'
[ ; ]
The name of the user to remove. @name_in_db is sysname, with no default. @name_in_db must exist in the current database. When specifying a Windows account, use the name by which the database knows that account.
0 (success) or 1 (failure).
sp_dropuser executes sp_revokedbaccess to remove the user from the current database.
Use sp_helpuser to display a list of the user names that can be removed from the current database.
When a database user is removed, any aliases to that user are also removed. If the user owns an empty schema with the same name as the user, the schema is dropped. If the user owns any other securables in the database, the user isn't dropped. Ownership of the objects must first be transferred to another principal. For more information, see ALTER AUTHORIZATION. Removing a database user automatically removes the permissions associated with that user and removes the user from any database roles of which it's a member.
sp_dropuser can't be used to remove the database owner (dbo) INFORMATION_SCHEMA users, or the guest user from the master or tempdb databases. In nonsystem databases, EXEC sp_dropuser 'guest' revokes CONNECT permission from user guest, but the user itself isn't dropped.
sp_dropuser can't be executed within a user-defined transaction.
Requires ALTER ANY USER permission on the database.
The following example removes the user Albert from the current database.
EXEC sp_dropuser 'Albert';
GO