SQL Server DROP LOGIN

Summary: in this tutorial, you will learn how to use the SQL Server DROP LOGIN statement to delete a login from the database server.

Introduction to the SQL Server DROP LOGIN statement

The DROP LOGIN statement allows you to delete a SQL Server login account. Here’s the syntax of the DROP LOGIN statement:

DROP LOGIN login_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you specify the login name that you want to delete after the DROP LOGIN keywords.

Note that you cannot drop a login while it’s signed in. And if a login owns a securable, server-level object, or SQL Server Agent job, you also cannot drop that login.

If a login account is mapped to database users and you drop a login, these database users will become orphaned users.

SQL Server DROP LOGIN statement example

We’ll use the BikeStores database in the following DROP LOGIN statement examples.

1) Simple DROP LOGIN statement example

First, create a new login called jack with a password:

CREATE LOGIN jack
WITH PASSWORD = 'iOvT84xn.';Code language: SQL (Structured Query Language) (sql)

Second, drop the login jack using the DROP LOGIN statement:

DROP LOGIN jack;Code language: SQL (Structured Query Language) (sql)

The statement was completed successfully because the login jack has no dependency. Typically, you use this scenario when you create a login with the wrong intended name.

2) Using the DROP LOGIN statement to remove a login that maps to a database user

First, create a new login called joe with a password:

CREATE LOGIN joe
WITH PASSWORD = 'NBgs23we$';Code language: SQL (Structured Query Language) (sql)

Second, create a new user for the login joe:

CREATE USER joe
FOR LOGIN joe;Code language: SQL (Structured Query Language) (sql)

Third, drop the login joe using the DROP LOGIN statement:

DROP LOGIN joe;Code language: SQL (Structured Query Language) (sql)

The user joe becomes orphaned.

To get all orphaned users in the current database server, you use the following query:

SELECT
  dp.type_desc,
  dp.sid,
  dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
  ON dp.sid = sp.sid
WHERE sp.sid IS NULL
AND dp.authentication_type_desc = 'INSTANCE';Code language: SQL (Structured Query Language) (sql)

Output:

3) Resolve an orphaned user

To resolve an orphaned user, you can recreate a missing login with the SID of the database user. For example, the following statement creates a new login ocean with the SID of the user joe:

CREATE LOGIN ocean
WITH PASSWORD = 'bNHXUYT321#',
	 SID = 0xC48461C284AE024EB42149BFDBCD18A8;Code language: SQL (Structured Query Language) (sql)

Now, the user joe can log in to the database server.

If you already have a login and want to map it with the orphaned user, you can use the ALTER USER statement. For example:

ALTER USER orphaned_user 
WITH LOGIN = login_name;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the DROP LOGIN statement to delete a login account from the SQL Server.
• The DROP LOGIN cannot delete a login account that is logged in or owns a securable, server-level object, or SQL Server Agent job.
• Drop a login that maps to database users will make these database users orphaned.