SQL Server DROP USER

Summary: in this tutorial, you’ll learn how to use the SQL Server DROP USER statement to delete a user from the current database.

Introduction to the SQL Server DROP USER statement #

The DROP USER statement allows you to delete a user from the current database. Here’s the syntax of the DROP USER statement:

DROP USER [IF EXISTS] user_name;Copy

In this syntax, you specify the name of the user that you want to delete after the DROP USER keyword. If the user doesn’t exist in the current database, the DROP USER statement will fail.

To avoid this, you can use the IF EXISTS option. The IF EXISTS option conditionally deletes the user if it already exists.

The DROP USER statement cannot delete a user that owns securables. To delete a user that owns securables, you need to:

• Drop securables or transfer ownership of these securables to another user.
• Drop the user.

The DROP USER statement also cannot drop the guest user. However, you can disable the guest user by revoking the CONNECT permission. The following statement revokes the CONNECT permission from the guest user:

REVOKE CONNECT FROM GUEST;Copy

Note that you execute the above statement in any databases other than master or tempdb:

SQL Server DROP USER statement example #

We’ll use the BikeStoressample database for the following DROP USER statement example.

1) Using the DROP USER to delete a user in the current database example #

First, create a new loginjin with a password:

CREATE LOGIN jin
WITH PASSWORD ='uJIKng12.';Copy

Second, create a new user and map it with the login jin:

CREATE USER jin
FOR LOGIN jin;Copy

Third, drop the user jin from the current database:

DROP USER IF EXISTS jin;Copy

2) Drop a user that owns a securable example #

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

CREATE LOGIN anthony
WITH PASSWORD ='uNMng78!';Copy

Second, create a new user for the login anthony:

CREATE USER tony
FOR LOGIN anthony;Copy

Third, create a schema called report and grant authorization to the user tony:

CREATE SCHEMA report 
AUTHORIZATION tony;Copy

Fourth, connect to the SQL Server using the login anthony and create a table called daily_sales in the schema report:

USE BikeStores;


CREATE TABLE report.daily_sales (
	Id INT IDENTITY PRIMARY KEY,
	Day DATE NOT NULL,
	Amount DECIMAL(10,2) NOT NULL DEFAULT 0
)Copy

Fifth, switch the connection to the system administrator (sa) account and drop the user tony:

DROP USER tony;Copy

SQL Server issued the following error:

The database principal owns a schema in the database, and cannot be dropped.Copy

Because the user tony owns the schema report, the DROP USER statement cannot delete it.

To remove the user tony, you need to transfer the authorization of the schema report to another user first. For example, the following statement changes the authorization of the schema report to the user dbo:

ALTER AUTHORIZATION 
ON SCHEMA::report 
TO dbo;Copy

If you execute the DROP USER statement to delete the user tony, you’ll see that it executes successfully:

DROP USER tony;Copy

Summary #

• Use the DROP USER statement to delete a user in the current database.
• If a user owns one or more securables, you need to transfer the ownership of the securables to another user before deleting the user.

Was this tutorial helpful?

YesNo