Fix SQL Server Error 15138: Dropping User that owned Schema & Error 3729: Schema that referenced to the object

Here we will learn how to fix Microsoft SQL Server error 15138 & error 3729 that we receive while dropping a database user and a schema in SQL Server.  Let us check the details to fix this issue one after another.

Microsoft SQL Server Error 15138

I have discussed similar type of issue in my last tip, where a server level login was owner of some databases which causes to fail the login removal and throws error 15174. We changed the ownership and then tried to drop that login and it was successful.

Microsoft SQL Server Error 15138 which i am discussing in this tip is also similar kind of error. Here, user has owned some schemas at database level. We will do the same thing, we will change the owner of identified schema and drop the user.

The details of SQL Server error 15138 is given below.

As error clearly says that a schema is owned by this user in the database so let us check the schema that is owned by this user. Please follow the below process to check the owned schema for an user in the database.

• Connect to SQL Server Instance in SSMS.
• Expand the database folder followed by the actual database name in which your user exists or created.
• Expand Security folder inside your target database followed by the Users folder and double click on the target user which you want to drop.
• You can see a section named “Owned Schemas. Here you can see the schemas that are owned by this user.

If you find any schema that is owned by this user in above steps, you need to change the owner from this user to some other user or you can change it to dbo as well. See the below screenshot of the owned schema for a user.

Solution

Now we have identified the schema which is owned by this user. Our Next step is to change is owner of the identified schema and then drop the login.

 --Change DBName with your database name and Schema_Name with your schema name which is owned by this user.
USE [DBName] 
G0 
Alter Authorization ON Schema::[SCHEMA_NAME] TO [dbo] 
Go

Once above command will execute, next step is to drop the user again. Run below command to drop the user.

--Change DBName with your database name where user exists and USERNAME with user name which you want to drop.
USE [DBName] 
Go 
DROP USER USERNAME 
Go

This time it will work and user will drop from the database. You can also delete this user from SSMS by right click on the user and choose the delete option. SSMS way is given below:

• Connect to target SQL Server Instance.
• Expand Database folder in which user exists.
• Expand the Security folder and then Users folder to get the target user name.
• Right click and choose delete on the identified user which needs to be deleted.
• Click on Ok button of the user deletion window.

There are many reasons which causes to fail the user or login deletion. You can find many errors and their solutions in given links. Learn to fix:

• Error Code 15174: Login Owns one or more databases(s). Change the owner of database(s) before dropping the login.
• Error Code 15173: Revoke the permission(s) before dropping the login.

Microsoft SQL Server Error 3729

We have seen how to drop an user who was owner of a schema in above section. Now we will understand how to drop a schema that is referenced by an object. If you try to run DROP Schema statement and that particular schema is referenced to some object you will get below error. The details of SQL Server error 3729 is given below.

In order to fix this issue, we will change the schema of table “Locations” to remove the reference. We will use sp_changeobjectowner system stored procedure to change schema from abc to dbo. Run below command to change the schema of the table “Locations” from abc to dbo.

--Change DBName with your database name where user/schema/table exists
--Change the abc to your schema name.
USE DBName
GO
sp_changeobjectowner 'abc.Locations','dbo'

Now go ahead and drop the schema post executing above command. This time it will drop without an error.

--Change DBName with your database name where user/schema/table exists
--Change the abc to your schema name which you want to delete.
USE [DBName]
GO
DROP SCHEMA [abc]
GO

You can also drop the same using SSMS as well. Go to schema folder inside the security folder of your database. Right click on identified schema and choose delete option.  it will delete your schema.

Learn to fix below errors as well:

• Error Code 15174: Login Owns one or more databases(s). Change the owner of database(s) before dropping the login.
• Error Code 15173: Revoke the permission(s) before dropping the login.

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.