sp_dropuser (Transact-SQL)
Removes a database user from the current database. sp_dropuser provides compatibility with earlier versions of SQL Server.
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use DROP USER instead.
.gif "Topic link icon")
Syntax
sp_dropuser [ @name_in_db = ] 'user'
Arguments
- [ @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.
Return Code Values
0 (success) or 1 (failure)
Remarks
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.
Permissions
Requires ALTER ANY USER permission on the database.
Examples
The following example removes the user Albert from the current database.
EXEC sp_dropuser 'Albert';
GO