I was recently contacted by a DBA colleague, asking for some assistance with a strange issue. He was trying to drop a user from a database, but the DROP USER (and sp_revokedbaccess) command was failing with the below error:
Msg 15183, Level 16, State 1, Line 5
The database principal owns objects in the database and cannot be dropped.
and
Msg 15138, Level 16, State 1, Line 8
The database principal owns a schema in the database, and cannot be dropped.
He’d managed to resolve the second error message, but not the first one. He has checked all the usual database objects like tables, views, stored procedures etc., but could not find any objects owned by the user being dropped. He was puzzled and had no idea what else could the user be owning.
I must admit, at first look, I couldn’t tell what’s preventing the user from being dropped. It doesn’t help that the error message thrown by SQL Server does not list the objects being owned by the user. Microsoft could clearly improve useability here, by providing more clarity in the error message. As a minimum, the error message should include at least one object that the user ownns. Then the DBA could drop that object and try deleting the user again.
At this point, we had a user that could not be dropped from the database, and we couldn’t tell what the user owned.
Intrigued with the situation, I put my detective hat on and set about finding that mysterious object owned by the user. My idea was to query all the “Object catalog views” and see if any of them show any objects owned by the principal_id of the user. I first tried all the usual catalog views like sys.tables, sys.views, sys.obejcts, sys.procedures etc with no luck.
Then I expanded my search by looking at other less populat catalog views, and I eventually landed on sys.event_notifications, which showed an event notification owned by this user. Got rid of the event notification (its not needed anyway), and success! We’ve managed to drop the user.
Bottom line is that, the SQL Server error message 15183 is not very helpful, as it doesn’t tell you which objects are owned by the user. It simply says “The database principal owns objects in the database and cannot be dropped“.
I am sure this is not an one off and others will continue to run into this issue too. To save time for others, and to help identify the objects owned by the user that could not be dropped, I’ve put together an SQL script. All you need to do is, simply update the name of the user on line number 8. As you can see from the code, it searches around 20 catalog views to identify any objects owned by the user in question.
The output of the query lists the objects owned by the user from line 8.
/*
Written by: Vyas Kondreddi
Website: https://dutyDBA.com/
*/
SET NOCOUNT ON
DECLARE @UserName sysname = 'AppUser'
DECLARE @PrincipalID int = USER_ID(@UserName), @CTR int = 1, @CMD nvarchar(4000)
DECLARE @Output table
(
OID int IDENTITY(1, 1),
ObjectType sysname,
OwnedObjectName sysname,
DataAvailableIn sysname
)
DECLARE @CatalogViews table
(
ViewID int IDENTITY(1, 1) PRIMARY KEY,
ViewName sysname,
ObjectDescription nvarchar(64) NOT NULL,
ColObjectName sysname,
ColPrincipalID sysname
)
INSERT INTO @CatalogViews (ViewName, ObjectDescription, ColObjectName, ColPrincipalID)
VALUES
('sys.schemas', 'Schema', 'name', 'principal_id'),
('sys.types', 'User Defined Data Type', 'name', 'principal_id'),
('sys.objects', 'From sys.objects', 'name', 'principal_id'),
('sys.assemblies', 'Assembly', 'name', 'principal_id'),
('sys.fulltext_catalogs', 'Full-Text Catalog', 'name', 'principal_id'),
('sys.fulltext_stoplists', 'Full-Text Stoplists', 'name', 'principal_id'),
('sys.registered_search_property_lists', 'Full-Text Search Properties', 'name', 'principal_id'),
('sys.database_scoped_credentials', 'Database Scoped Credential', 'name', 'principal_id'),
('sys.asymmetric_keys', 'Asymmetric Keys', 'name', 'principal_id'),
('sys.certificates', 'Certificates', 'name', 'principal_id'),
('sys.security_policies', 'Security Policy', 'name', 'principal_id'),
('sys.symmetric_keys', 'Symmetric Keys', 'name', 'principal_id'),
('sys.remote_service_bindings', 'Service Broker Remote Service Binding', 'name', 'principal_id'),
('sys.routes', 'Service Broker Route', 'name', 'principal_id'),
('sys.service_contracts', 'Service Broker Service Contract', 'name', 'principal_id'),
('sys.service_message_types', 'Service Broker Service Message Type', 'name', 'principal_id'),
('sys.service_queues', 'Service Broker Service Queue', 'name', 'execute_as_principal_id'),
('sys.services', 'Service Broker Service', 'name', 'principal_id'),
('sys.xml_schema_collections', 'XML Schema Collection', 'name', 'principal_id'),
('sys.event_notifications', 'Event Notification', 'name', 'principal_id')
WHILE @CTR IS NOT NULL
BEGIN
SELECT
@CMD = 'SELECT ' +
QUOTENAME(ObjectDescription, '''') + ', ' +
QUOTENAME(ColObjectName) + ',' +
QUOTENAME(ViewName, '''') + '
FROM ' + ViewName + '
WHERE ' + QUOTENAME(ColPrincipalID) + ' = ' + CAST(@PrincipalID AS varchar(8))
FROM
@CatalogViews
WHERE
ViewID = @CTR
IF @@ROWCOUNT = 0 BREAK
BEGIN TRY
INSERT INTO @Output (ObjectType, OwnedObjectName, DataAvailableIn)
EXEC sp_executesql @CMD
END TRY
BEGIN CATCH
RAISERROR('************ Below error message encountered while running %s', 0, 1, @CMD)
PRINT ERROR_MESSAGE()
END CATCH
SET @CTR += 1
END
SELECT
ObjectType,
OwnedObjectName,
DataAvailableIn
FROM
@Output
ORDER BY
OIDNotice the INSERT statement starting line 28? That INSERT statement defines which catalog views are searched by this query. If you want to search any additional views, you can simply expand the search by adding any addititional row via that INSERT statement in the format:
(‘sys.event_notifications’, ‘Event Notification’, ‘name’, ‘principal_id’)
The above row in the INSERT statement provides four comma separated values. The first value is the catalog view that needs to be searched, second one is a label to say what that catalog view contains, third one is the column name that contains the name of the object, and the last and fourth value is the column name that contains the user ID, which in most cases is principal_id.
Note: Whilst this article is about not being able to drop a user from a database, as some objects are owned by that user within the dataase – its possible to encounter a similar issue when dropping a login at server level.
If you are struggling to drop a login at server level as it owns some server level objects, but no obvious objects are found to be owned by the login, look into server level catalog views like sys.server_file_audits, sys.server_audits, sys.endpoints. I once had a scenario, where an Always On endpoint was owned by an individual login other than ‘sa’. In that case, I had to change the ownership of the endpoint using ALTER AUTHORIZATION command, before I could drop the login.
