sp_dropuser
Removes a Microsoft® SQL Server™ user or Microsoft Windows NT® user from the current database. sp_dropuser is provided for backward compatibility. Use sp_revokedbaccess to remove a user.
Syntax
sp_dropuser [ @name_in_db = ] 'user'
Arguments
[@name_in_db =] 'user'
Is the name of the user to remove. user is sysname, with no default. user must exist in the current database. When specifying a Windows NT user, specify the name that the Windows NT user is known by in the database (added using sp_grantdbaccess).
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 usernames that can be removed from the current database.
When the security account for a user is removed, any aliases to that user are also removed. A user cannot be removed if the user owns any objects in the database. Ownership of the objects must be changed to another user using sp_changeobjectowner. Removing a user automatically removes the permissions associated with the user and removes them from any roles of which the user is a member.
sp_dropuser cannot be used to remove the dbo or INFORMATION_SCHEMA users, nor the guest user from the master or tempdb databases.
sp_dropuser cannot be executed from within a user-defined transaction.
Permissions
Only members of the sysadmin fixed server role, the db_owner or db_accessadmin fixed database roles can execute sp_dropuser.
Examples
This example removes the user Albert from the current database.
EXEC sp_dropuser 'Albert'