---
title: "How to drop a user in SQL Server?"  
description: "How to drop a user in SQL Server?"  
author: "Sandra Emily"  
published: 2024-07-16  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160924/how-to-drop-a-user-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# How to drop a user in SQL Server?

How to [drop](https://www.mindstick.com/forum/159392/how-to-fetch-the-value-of-the-selected-options-in-drop-down-in-php) a user in SQL Server?

## Replies

### Reply by Ashutosh Patel

#### SQL Server DROP USER

The SQL statement `DROP USER` allows you to delete a **user** from an existing database. Here is the syntax of the `DROP USER` statement

```plaintext
DROP USER [IF EXISTS] user_name;
```

In the syntax above, don't forget to replace your use name with the `user_name` after the keywork `DROP USER`. If the user doesn't exist in the current database then `DROP USER` statement will fail.

To avoid this, you can use the `IF EXISTS` option. If the user already exists, the `IF EXISTS` option conditionally deletes them.

#### 1. Using the DROP USER statement to delete a user

Now, use the `MyCollegeDb` sample database for the following `DROP USER` statement example.

First, create a new login `testLogin` with a password,

```plaintext
CREATE LOGIN testLogin
WITH PASSWORD='UyxIv@12'
```

Second, [create a new user](https://www.mindstick.com/forum/160922/how-to-create-user-and-grant-permission-in-sql-server) and map it with the login `testLogin`

```plaintext
USE MyCollegeDb
GO

CREATE USER MyUser
FOR LOGIN testLogin;
```

Third, drop the user `MyUser` from the current database

```plaintext
USE MyCollegeDb
GO
DROP USER IF EXISTS MyUser;
```

#### 2. Drop a user that owns a securable

First, create a new login called `jacob` with a password,

```plaintext
USE master
GO
CREATE LOGIN jacob
WITH PASSWORD = 'Jac.B908$#'
```

Second, use your current database `MyCollegeDb` and create a new user for the login `jacob`

```plaintext
USE MyCollegeDb
GO
CREATE USER ami
FOR LOGIN jacob;
```

Third, create a schema called `report` and grant authorization to the user `ami`.

```plaintext
USE MyCollegeDb
GO
CREATE SCHEMA report
AUTHORIZATION ami;
```

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

```plaintext
USE MyCollegeDb
GO

CREATE TABLE report.daily_sales (
Id INT IDENTITY PRIMARY KEY,
CreationDate DATETIME NOT NULL,
Amount DECIMAL(10,2) NOT NULL DEFAULT 0
)
```

Fifth, switch the connection to the system administrator (`sa`) account and drop the user `ami`

```plaintext
USE MyCollegeDb
GO

DROP USER ami;
```

The SQL server Returns the following error,

![How to drop a user in SQL Server?](https://www.mindstick.com/mindstickforums/cb265741-57ec-4531-b2c8-1c53727621f4/images/ef0c179a-3ec3-4680-b2d9-c16a957422bc.jpg)

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

To delete user `ami`, you must first transfer the authorization of the schema report to another user. For example, the following statement changes the authorization of the schema `report` to user `dbo`:

```plaintext
USE MyCollegeDb
GO
ALTER AUTHORIZATION
ON SCHEMA::report
TO dbo;
```

If you execute the `DROP USER` statement to delete the user `ami`, it will executes successfully,

```plaintext
USE MyCollegeDb
GO
DROP User ami;
```

**Also, Read:** [Describe the different types roles in SQL Server.](https://www.mindstick.com/forum/160905/describe-the-different-types-roles-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160924/how-to-drop-a-user-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.