Today I was performing
audit on Database users and I received below
error while deleting the invalid login-
The database principal owns a schema in the database, and
cannot be dropped. (Microsoft SQL Server, Error: 15138)
Workaround
/ Resolution / Fix:
Let us assume that user was trying to
delete user which is named as ‘pawan’ and it exists in the database ‘SQLOPSDB’.
Now run following script with the
context of the database where user belongs.
USE SQLOPSDB;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pawan');
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('pawan');
In my query I get following two schemas
as a result-
Now
let us run following queries where I will take my schema and alter
authorization on schema. In my case I have two schemas so I will execute it two
times.
ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;ALTER
AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;
Now
if you drop the database owner it will not throw any error.
Below
is generic script for resolving the error:
SELECT s.nameFROM sys.schemas sWHERE s.principal_id = USER_ID('YourUserID');Now replace the result name in following script:
ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;
