sp_droplogin
Removes a Microsoft® SQL Server™ login, preventing access to SQL Server using that login name.
Syntax
sp_droplogin [ @loginame = ] 'login'
Arguments
[@loginame =] 'login'
Is the login to be removed. login is sysname, with no default. login must already exist in SQL Server.
Return Code Values
0 (success) or 1 (failure)
Remarks
A login mapped to an existing user in any database cannot be removed. The user must be removed first by using sp_dropuser. Additionally, these logins cannot be removed:
- The system administrator (sa) login.
- A login that owns an existing database.
- A login that owns jobs in the msdb database.
- A login that is currently in use and connected to SQL Server.
Use sp_changedbowner to change the owner of a database.
Removing a login also deletes any remote and linked server logins mapped to the login.
sp_droplogin cannot be executed within a user-defined transaction.
Permissions
Only members of the sysadmin and securityadmin fixed server roles can execute sp_droplogin.
sp_droplogin must check all databases on the server to determine if any user accounts in those databases are associated with the login being deleted. Therefore, for each database on the server, one of these must apply:
- The user executing sp_droplogin must have permissions to access the database.
- The guest user account must exist in the database.
If a database cannot be accessed, the login can still be deleted. However, error message 15622 is generated and any users who were associated with the deleted login become orphaned in the databases that could not be accessed. To determine the orphaned users, execute sp_change_users_login REPORT in each database that could not be accessed by sp_droplogin.
Examples
This example removes the login Victoria from SQL Server.
EXEC sp_droplogin 'Victoria'