Removing SQL Server Users: A Guide to Using the DROP USER Statement

SQL Server DROP USER statement

If you are working with SQL Server, you may find yourself needing to remove a user from the system. This could be due to many reasons, like an employee leaving a company or a user no longer requiring access to a certain database.

In this article, we will examine the DROP USER statement, one of the SQL Server utilities you can use to remove a user account from your system.

Syntax of the DROP USER statement

To drop a user account, you would use the following syntax:

DROP USER user_name

Where user_name is the name of the user account you want to remove from the system. Make sure to include the name in single quotes if it contains spaces.

IF EXISTS option of the DROP USER statement

You can also use the IF EXISTS option in the syntax to avoid any errors when trying to drop a user account that doesn’t exist. This option allows you to check first if the user account exists and then drop it.

Here’s how to use the IF EXISTS option in the DROP USER statement:

DROP USER IF EXISTS user_name

Limitations of the DROP USER statement

While the DROP USER statement is a helpful utility in SQL Server, it comes with a few limitations. One of those limitations includes the guest user, which cannot be dropped.

The guest user is automatically created with every new database, and SQL Server uses it to grant access to logins that haven’t been given explicit permission. Another limitation is that if the user owns any securables, like tables, views, stored procedures, or schemas, you will not be able to drop the user until you transfer ownership or delete the securables.

If you try to drop a user account that owns a schema without transferring the ownership first, you will get an error message like this:

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

Example of using the DROP USER statement to delete a user that owns a securable

Let’s go through an example of how to use the DROP USER statement to delete a user that owns a securable. In this example, we have a login called Anthony and a user account called Tony, which owns a report schema that we need to delete:

Creating a new login

First, we’ll create a new login for Anthony by using the following SQL query:

CREATE LOGIN Anthony WITH PASSWORD='P@ssw0rd'

Creating a new user for the login

Next, we’ll create a new user for the login called Tony by using this SQL query:

CREATE USER Tony FOR LOGIN Anthony

Creating a schema and granting authorization

Now let’s create a schema and grant authorization for the Tony user by using the following SQL query:

CREATE SCHEMA report AUTHORIZATION Tony

Creating a new table and switching to the sa account

Next, let’s create a new table under the report schema and switch to the sa account to simulate a different user:

CREATE TABLE report.sales (
    sale_id INT IDENTITY(1,1) PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    quantity_sold INT NOT NULL
)

GO

USE master

GO

EXECUTE AS LOGIN='sa'

Error in dropping the user due to owning a schema

Now, let’s try to delete the Tony user account with the DROP USER statement by using this SQL query:

DROP USER Tony

But we’ll get an error message like this:

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

This error message displays because the Tony user account owns the “report” schema and, as a result, the system does not allow you to delete the user account without first transferring the schema ownership.

Transferring schema authorization to another user and successfully dropping the user

To transfer authority to another user, we first need to create a new user account in SQL Server. For this example, we’ll create a new user called “Mike” by using the following SQL query:

CREATE USER Mike FOR LOGIN Anthony

Now we will transfer the schema authorization from Tony to Mike by anyone of the following SQL query depending on what type of schema you have:

For schemas, views, and stored procedures:

ALTER AUTHORIZATION ON SCHEMA::report TO Mike; 

For a table:

ALTER AUTHORIZATION ON report.sales TO Mike;

Now with the new user Mike authorized to use the schema under the report, we can successfully drop the Tony user by using the DROP USER statement, as shown below:

DROP USER Tony

Conclusion

As you can see, the DROP USER statement in SQL Server is an essential utility for any database administrator or developer. By using this statement, you can remove a user’s account from your system, but you may encounter limitations from time to time.

Remember to use the IF EXISTS option in your syntax, and ensure you transfer the ownership of securables like schemas to another user account before deleting a user. In summary, the DROP USER statement is an essential utility in SQL Server that allows you to remove user accounts from the system.

However, you may experience limitations depending on the securables and ownerships associated with the user’s account. To address these limitations, you can use the IF EXISTS option in your syntax, and transfer ownership of securables like schemas to other user accounts before removing a user.

Remembering these limitations is crucial to the effective management of SQL Server and ensuring the security of your databases.