sp_dropuser (T-SQL)

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 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'

See Also