The database principal owns objects in the database and cannot be dropped

Question

Post reply

The database principal owns objects in the database and cannot be dropped

zhazhuzhao

Say Hey Kid

Points: 661
More actions
May 3, 2013 at 12:57 am

#272220

Hi guys, Recently we got this error:
The database principal owns objects in the database and cannot be dropped.
The database version is SQL Server 2008,
I have checked that user doesn't have schema,procedure,function,table,view and so on.
My question is: how to check the user's objects?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 1

zhazhuzhao

Say Hey Kid

Points: 661

May 3, 2013 at 1:45 am

#1612094

Anyone can help is very appreciate!

Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 2

this blog has a good explanation and the scripts to track down the offending schema:

http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

Can't remember where I found it, use this script to determine which objects are owned by the user

;with objects_cte as

(

select

o.name,

o.type_desc,

case

when o.principal_id is null then s.principal_id

else o.principal_id

end as principal_id

from sys.objects o

inner join sys.schemas s

on o.schema_id = s.schema_id

where o.is_ms_shipped = 0

and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')

)

select

cte.name,

cte.type_desc,

dp.name

from objects_cte cte

inner join sys.database_principals dp

on cte.principal_id = dp.principal_id

where dp.name = 'user_name'

Use this script to change the owner to dbo

alter authorization on object_name to dbo

sqldbalearner SSC Enthusiast Points: 129 More actions · Answer 4

Even i had faced the same issue but user was a database owner in my case, so i tried like

changing EXEC Sp_changedbowner 'SA' and tried dropping the user it worked.

We may need to give ownership to other user of objects or database to other before deleting the object owner

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 5

SQLSACT (5/3/2013)
Can't remember where I found it, use this script to determine which objects are owned by the user
;with objects_cte as
(
select
o.name,
o.type_desc,
case
when o.principal_id is null then s.principal_id
else o.principal_id
end as principal_id
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
cte.name,
cte.type_desc,
dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name = 'user_name'
Use this script to change the owner to dbo
alter authorization on object_name to dbo

Hi, I tried this sql and it returns no data! Also this database user is not a db_owner user!

Thanks all the same!

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 6

Lowell (5/3/2013)
this blog has a good explanation and the scripts to track down the offending schema:
http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/

Hi, also I tried this way, and the sql didn't return data! I check that use didn't have schema!

Thanks all the same!

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

zhazhuzhao (5/5/2013)
SQLSACT (5/3/2013)
Can't remember where I found it, use this script to determine which objects are owned by the user
;with objects_cte as
(
select
o.name,
o.type_desc,
case
when o.principal_id is null then s.principal_id
else o.principal_id
end as principal_id
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
cte.name,
cte.type_desc,
dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name = 'user_name'
Use this script to change the owner to dbo
alter authorization on object_name to dbo
Hi, I tried this sql and it returns no data! Also this database user is not a db_owner user!
Thanks all the same!

Silly question - Are you executing the script against the correct database?

suneet.mlvy SSC Eights! Points: 964 More actions · Answer 8

Are u sure there is no DB or schema connected to that user.

plz try this.

Use [Master]

select name, default_database_name from sys.sql_logins

go

Use [User_name]

select * from sys.user_token

katukuri.sqldba Mr or Mrs. 500 Points: 531 More actions · Answer 9

Hi there,

Did you note the error number, that would help a lot to fix.

And Do you have the witness server, Run the script given above, that should fix.

thanks

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 10

Sure, I run it in the correct database!

SQLSACT (5/5/2013)
zhazhuzhao (5/5/2013)
SQLSACT (5/3/2013)
Can't remember where I found it, use this script to determine which objects are owned by the user
;with objects_cte as
(
select
o.name,
o.type_desc,
case
when o.principal_id is null then s.principal_id
else o.principal_id
end as principal_id
from sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
and o.type in ('U', 'FN', 'FS', 'FT', 'IF', 'P', 'PC', 'TA', 'TF', 'TR', 'V')
)
select
cte.name,
cte.type_desc,
dp.name
from objects_cte cte
inner join sys.database_principals dp
on cte.principal_id = dp.principal_id
where dp.name = 'user_name'
Use this script to change the owner to dbo
alter authorization on object_name to dbo
Hi, I tried this sql and it returns no data! Also this database user is not a db_owner user!
Thanks all the same!
Silly question - Are you executing the script against the correct database?

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 11

Hi, I have run this script! I didn't find it has relation with that user I want to drop!

It's only a database user from windows domain login! The login has been droped!

suneet.mlvy (5/6/2013)
Are u sure there is no DB or schema connected to that user.
plz try this.
Use [Master]
select name, default_database_name from sys.sql_logins
go
Use [User_name]
select * from sys.user_token

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 12

Hi, I got this error:

Msg 15183, Level 16, State 1, Line 1

The database principal owns objects in the database and cannot be dropped.

katukuri.sqldba (5/6/2013)
Hi there,
Did you note the error number, that would help a lot to fix.
And Do you have the witness server, Run the script given above, that should fix.
thanks

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 13

I use this script to find which object the user have:

select 'select * from ['+s.name+'].['+o.name+'] where ['+c.name +']=User_ID(''LoginName'')'

FROM sys.all_columns c

JOIN sys.all_objects o ON o.object_id = c.object_id

join sys.schemas s ON o.schema_id = s.schema_id

WHERE c.name LIKE '%principal_id%'

and o.name not like 'fn_%'

zhazhuzhao Say Hey Kid Points: 661 More actions · Answer 14

zhazhuzhao (6/17/2013)
I use this script to find which object the user have:
select 'select * from ['+s.name+'].['+o.name+'] where ['+c.name +']=User_ID(''LoginName'')'
FROM sys.all_columns c
JOIN sys.all_objects o ON o.object_id = c.object_id
join sys.schemas s ON o.schema_id = s.schema_id
WHERE c.name LIKE '%principal_id%'
and o.name not like 'fn_%'