神刀安全网

SQL Server errors with drop login and drop user

Problem

I was trying to cleanup my SQL Server logins and users, but when I tried to drop a login I got this error message "Login ‘x’ owns one or more database(s). Change the owner of the database(s) before dropping the login.".  Also, when I tried to drop a database user I got this error message "The database principal owns a schema in the database, and cannot be dropped. (error 15138)".  What are the steps to successfully drop SQL Server logins and users in these cases?

Solution

There are two access levels in SQL Server; access at the server level is granted via SQL Server or Windows Authentication logins, and access at the database level is granted via database users. The server login is also mapped to a database user in order to grant access at the database level.

In this tip, we will concentrate on how to resolve cases in which you may not be able to drop the server login or the database user as it is connected to a specific database or server object.

Login ‘x’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

How to drop a login that owns a database

The first scenario is when you try to drop a server login that is owner of a specific database. In our demonstration, we will try to drop the MSSQLTipsUser server login simply by applying the below DROP LOGIN statement:

USE [master] GO DROP LOGIN [MSSQLTipsUser] GO

The statement will fail with the below error:

Login ‘MSSQLTipsUser’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

A similar error will appear if we try to drop it using the SSMS:

SQL Server errors with drop login and drop user

As we can clearly see from the errors, this login can’t be dropped as it is the owner of one of the databases. In order to check which database this login owns, we will query the sys.databases system table for the database owners using the below simple script:

SELECT name, suser_sname(owner_sid) AS DBOwner FROM sys.databases

From the query result below, the MSSQLTipsUser is the owner of the MSSQLTipsDemo database.

SQL Server errors with drop login and drop user

In order to make it possible to drop the login, we need to change the MSSQLTipsDemo database owner to SA (or some other user) using the sp_changedbowner system stored procedure as follows:

use MSSQLTipsDemo  GO sp_changedbowner 'sa'

Below we can see the owner of the MSSQLTipsDemo database has been changed to SA:

SQL Server errors with drop login and drop user

If we try to drop the login MSSQLTipsUser again, it will be dropped successfully:

SQL Server errors with drop login and drop user

The database principal owns a schema in the database, and cannot be dropped. (error 15138)

How to drop a user that owns a schema

The second scenario, when we try to drop a database user that is owns a database schema. In our demonstration here, we will try to drop the MSSQLTipsUser database user simply by applying the below DROP USER statement:

USE [MSSQLTipsDemo] GO DROP USER [MSSQLTipsuser] GO

The statement will fail getting the below error:

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

The same error will appear if you try to drop it using SSMS:

SQL Server errors with drop login and drop user

It is clear from the error that the MSSQLTipsuser database user can’t be dropped, as it owns one of the database schemas. To find which schema this user owns we need to browse the user in the database security node, then go to the user’s properties using SSMS. From the Owned Schemas tab of the user’s properties window, we can find that the user owns the db_owner database schema:

SQL Server errors with drop login and drop user

In order to make it possible to drop that database user, we need to change the owner of the db_owner schema to another user, which will be dbo in our case. To achieve this, we will use the ALTER AUTHORIZATION ON SCHEMA T-SQL statement as follows:

USE [MSSQLTipsDemo] GO ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [DBO] GO

If we try to drop the database user again, it will be dropped successfully:

SQL Server errors with drop login and drop user

How to drop a user that owns a schema and the schema is referenced by an object

The last scenario, when we try to drop a database user that owns a database schema and also this schema is referenced by a database object. In our demonstration here, if we will try to drop the MSSQLTipsUser database user simply by applying the below DROP USER statement:

USE [MSSQLTipsDemo] GO DROP USER [MSSQLTipsuser] GO

The statement will fail getting the below error:

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

If we try to get the schema that is owned by this user from the user’s properties, it is found that this user owns the MSSQLTipsuser schema:

SQL Server errors with drop login and drop user

If we try to drop this schema using the DROP SCHEMA T-SQL statement below:

USE [MSSQLTipsDemo] GO DROP SCHEMA [MSSQLTipsUser] GO

The query will fail getting the below error:

Cannot drop schema ‘MSSQLTipsUser’ because it is being referenced by object ‘CountryInfoNew’.

As we can see from this error, this schema is used by the CountryInfoNew table.

The same two errors will be shown if we try to drop the user using SSMS. As you can see from these errors, SQL Server tried first to drop the schema then the user:

SQL Server errors with drop login and drop user

This error resulted from dropping the database schema:

SQL Server errors with drop login and drop user

This error resulted from dropping the database user:

SQL Server errors with drop login and drop user

In order to resolve this issue, we will change the schema of the CountryInfoNew table in order to break any reference to that schema using the sp_changeobjectowner system stored procedure as below:

sp_changeobjectowner 'MSSQLTipsuser.CountryInfoNew','dbo'

Now if we try to drop the schema again, it will be dropped successfully, as it is not referenced by any object:

USE [MSSQLTipsDemo] GO DROP SCHEMA [MSSQLTipsuser] GO

We can now drop the database user as it does not own any schemas:

SQL Server errors with drop login and drop user

Next Steps

Last Update:

SQL Server errors with drop login and drop user

About the author

SQL Server errors with drop login and drop user Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

View all my tips

Related Resources

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » SQL Server errors with drop login and drop user

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址