| title | sp_dropuser (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.service | |||
| ms.component | system-stored-procedures | ||
| ms.reviewer | |||
| ms.suite | sql | ||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | e28f18f9-7ecf-4568-89f4-fe5c520df386 | ||
| caps.latest.revision | 21 | ||
| author | edmacauley | ||
| ms.author | edmaca | ||
| manager | craigg | ||
| ms.workload | On Demand |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Removes a database user from the current database. sp_dropuser provides compatibility with earlier versions of [!INCLUDEssNoVersion].
Important
[!INCLUDEssNoteDepFutureAvoid] Use DROP USER instead.
Transact-SQL Syntax Conventions
sp_dropuser [ @name_in_db = ] 'user'
[ @name_in_db =] 'user'
Is the name of the user to remove. user is a sysname, with no default. user must exist in the current database. When specifying a Windows login, use the name by which the database knows that login.
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 will be dropped. If the user owns any other securables in the database, the user will not be dropped. Ownership of the objects must first be transferred to another principal. For more information, see ALTER AUTHORIZATION (Transact-SQL). Removing a database user automatically removes the permissions associated with that user and removes the user from any database roles of which it is a member.
sp_dropuser cannot 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' will revoke CONNECT permission from user guest. But the user itself will not be dropped.
sp_dropuser cannot 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
Security Stored Procedures (Transact-SQL)
sp_grantdbaccess (Transact-SQL)
DROP USER (Transact-SQL)
sp_revokedbaccess (Transact-SQL)
System Stored Procedures (Transact-SQL)