Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The database principal owns objects in the database and cannot be dropped Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 9, Visits: 65
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?
Post #1449064
Posted Friday, May 3, 2013 1:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 9, Visits: 65
Anyone can help is very appreciate!
Thanks!
Post #1449071
Posted Friday, May 3, 2013 5:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 12,903, Visits: 31,970
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449136
Posted Friday, May 3, 2013 5:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,375, Visits: 2,663
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

Post #1449145
Posted Saturday, May 4, 2013 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 11:50 AM
Points: 5, Visits: 60
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
Post #1449405
Posted Sunday, May 5, 2013 8:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 9, Visits: 65
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!
Post #1449563
Posted Sunday, May 5, 2013 8:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:09 AM
Points: 9, Visits: 65
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!
Post #1449564
Posted Sunday, May 5, 2013 11:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 1,375, Visits: 2,663
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?
Post #1449584
Posted Monday, May 6, 2013 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:32 AM
Points: 34, Visits: 70
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
Post #1449686
Posted Monday, May 6, 2013 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:52 AM
Points: 9, Visits: 89
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
Post #1449692
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse