CODEWITHSUNDEEP
sql-serverdatabaseschemarolesql-drop
sanjeev40084
sanjeev40084

Reputation: 9617

Drop role in SQL Server database?

I am trying to drop one of the role in my SQL Server database. I dropped all the members from the role and when i tried to drop role i got this error message:

Msg 15138, Level 16, State 1, Line 13
The database principal owns a schema in the database, and cannot be dropped.

Does anyone know why? I checked the Owned Schema and it only had check sign in its own name.

Upvotes: 1

Views: 6789

Answers (2)

Nagaraj Raveendran
Nagaraj Raveendran

Reputation: 1235

15138 error is due to the user you are trying to delete owns a schema.

If you run the below query you will get the schema owned by the user.

USE DatabaseName; 
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserName');

Let us say it returns 'db_denydatareader' schema. Then you can assign that schema to default user 'dbo' using the below query.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;

Upvotes: 4

Aaron M
Aaron M

Reputation: 2563

You cannot drop a database principal that owns a schema. You have to transfer the schema ownership to some other database principal or drop the schema before you can drop the database principal.

Upvotes: 3

Related Questions