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

Msg 15138, Level 16, State 1, Line 5 The database principal owns a schema in the database, and cannot be dropped.
Things constantly change and one such thing is the addition and removal of users and servers.  In an attempt to remove a Machine account SQL Server login that was being replaced we know that removing a user from an Instance is not the same as removing them from a databases.  The Instance removal was simple however I came across this error message at the database level "The database principal owns a schema in the database, and cannot be dropped. (Msg 15138, Level 16, State 1, Line 5)".  

Here are some steps to successfully review and drop the SQL Server user database logins:
1. What schema(s) does the user own?
The following example finds the user and each schema owner.
SELECT s.name, USER_NAME(s.principal_id) AS Schema_Owner
FROM sys.schemas s
-- WHERE s.principal_id = USER_ID('You user Name');

B. Now reassign or drop the schema the choice is up to you.

Quick and dirty would be to simply reassign the schema to [dbo].  If you can confirm that it's not in use or will not ever be used then drop it.  If there is an existing reference or something you missed and you do attempt to drop the schema it will fail with a message similar to the following: " Msg 3729, Level 16, State 1, Line 29 Cannot drop schema 'Your Schema' because it is being referenced by object 'Something'. " 



USE [Your Database Name]
GO

ALTER AUTHORIZATION ON SCHEMA::[Your Schema] TO [dbo]
-- DROP SCHEMA [You Schema name]
GO

C. Schema reference to a Table

If the Schema is used in a table name then you will need to do more research and ensure there isn't other references in stored procedures or view or even triggers.  So assuming you have done your research you are now free to us sp_changeobjectowner 'Old Schema.TableName','new schema'.
sp_changeobjectowner 'YourSchema.table','dbo'

D. Finally Drop User from database and verify that they do not exist anywhere that you would not expect.

Use the below to identify if a user exists on any database across the entire SQL Instance for all databases that are online and accessible.  Update 'User2Search4' to be the login you are looking for.  Note that in the last few lines we are doing a like serach on the login being searched so it can be changed as need.

SET NOCOUNT ON;
-- Update the below 'User2Search4' for the User to verify if Exists and in what database
DECLARE @LoginName VARCHAR(255)='User2Search4'

CREATE TABLE #temp (SERVER_name SYSNAME NULL,Database_name SYSNAME NULL,UserName SYSNAME,GroupName SYSNAME
,LoginName SYSNAME NULL,DefDBName SYSNAME NULL,DefSchemaName SYSNAME NULL,UserID INT,[SID] VARBINARY(85));

DECLARE @command VARCHAR(MAX);
--this will be used for all the databases and their sizes on a server
DECLARE @databases TABLE (Database_name VARCHAR(255),Database_size INT,remarks VARCHAR(Max));

INSERT INTO @databases -- Load with the list of databases
EXEC sp_databases;

SELECT @command = COALESCE( @command, '' ) + ' USE '         + database_name + '
    insert into #temp (UserName,GroupName, LoginName,DefDBName, DefSchemaName,UserID,[SID])
         Execute sp_helpuser
    UPDATE #TEMP SET database_name=DB_NAME(),server_name=@@ServerName where database_name is null'
  FROM @databases;
EXECUTE (@command);

DECLARE @LoginName2 VARCHAR(255)='%'+@LoginName+'%'

SELECT loginname, UserName, Database_name 
FROM #temp WHERE LoginName LIKE @LoginName2;

DROP TABLE #temp


Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more