SQL Server Drop Database User

ByHariharan Rajendran

SQL Server Drop Database User

This blog post explains the issue that we usually face while dropping the users from the database and how can we resolve the issue.

We can create a user for database easily by any of the following user types,

  1. SQL User with login
  2. SQL User without login
  3. User mapped to a certificate
  4. User mapped to a asymmetric key
  5. Windows User

If we want to drop the user then we can write drop statement but it will throw a below error in case the user owned any of the schemas.

Msg 15138, Level 16, State 1, Line 20

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

To resolve the issue, Follow the below steps.

Step 1: Check the schema that owned by the user.

SELECT *

FROM sys.schemas s

WHERE s.principal_id = USER_ID(‘Username’)

Step 2: Change the schema back to dbo.

ALTER AUTHORIZATION ON SCHEMA::<schemaname> TO dbo;

Step 3: Drop the user

DROP USER <username>

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Leave a Reply