Removes a Microsoft® SQL Server™ login, preventing access to SQL Server using that login name.
sp_droplogin [@loginame =] 'login'
0 (success) or 1 (failure)
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:
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.
Only members of the sysadmin or securityadmin fixed server roles can execute sp_droplogin.
Note 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:
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.
This example removes the login Victoria from SQL Server.
EXEC sp_droplogin 'Victoria'
sp_addlogin | sp_dropuser |
sp_changedbowner | sp_helpuser |
sp_change_users_login | System Stored Procedures |