how do I determine why I cannot drop a schema

by JJS   Last Updated February 11, 2019 10:06 AM - source

A schema was created with the name of a windows user [DOMAIN\Foo]. I receive this error message when attempting to drop the schema.

DROP SCHEMA [DOMAIN\foo]

Msg 15151, Level 16, State 1, Line 8
Cannot drop the schema 'DOMAIN\foo', because it does not exist or you do not have permission.
The statement has been terminated.

I have already made sure that the user does not have authorization on the schema, and transferred ownership to dbo

alter authorization on schema::[DOMAIN\foo] to dbo

  • My windows login is a member of the server role sysadmin.
  • My user mapped to the database is a member of the database role db_owner.


Answers 1


The root of the problem is that I have a schema-only database defined that is generated automatically via triggers. When a DDL statement gets replayed by the INSERT trigger of the changelog table, the error occurs when the schema is out of sync (The schema did not exist in my SchemaOnlyDatabase). The error was being thrown in the context of the SchemaOnlyDatabase, not the database that was currently active TheDevDatabase, but that was not clearly indicated by the message.

CREATE TRIGGER [RecordDatabaseChanges]                                
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS
AS                                                      
BEGIN
SET NOCOUNT ON;
--ALL SETTINGS NEEDED FOR XQUERY IN DDL TRIGGERS
--http://tomaslind.net/2013/10/15/zero-footprint-serverdatabase-ddl-trigger/
SET ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
DECLARE @data XML;
SET @data = EVENTDATA();
DECLARE @EventType NVARCHAR(30)
SELECT @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)')
IF @EventType IN (
N'ADD_ROLE_MEMBER',
N'ALTER_AUTHORIZATION_DATABASE',
N'ADD_SIGNATURE_SCHEMA_OBJECT',
N'DENY_DATABASE',
N'DROP_ROLE_MEMBER',
N'GRANT_DATABASE',
N'REVOKE_DATABASE',
N'UPDATE_STATISTICS'
)
RETURN
INSERT INTO [ChangeLogDb].[dbo].[ChangeLog] ([Event Type], [Database], UserName, SPID, [Schema], Name, [New Name], ObjectType, [New ObjectType], [SQL Used])
SELECT  
COALESCE(@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(30)'),'') AS [Event Type],
COALESCE(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)'),'') AS [Database],
COALESCE(@data.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(128)'),'') AS [UserName],
COALESCE(@data.value('(/EVENT_INSTANCE/SPID)[1]','nvarchar(128)'),'') AS [SPID],
COALESCE(@data.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(128)'),'') AS [Schema],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(128)'),'') AS [Name],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(128)'),'') AS [New Name],
COALESCE(@data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(128)'),'') AS [ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TargetObjectType)[1]','nvarchar(128)'),'') AS [New ObjectType],
COALESCE(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),'') AS [SQL Used]
END

CREATE TABLE [ChangeLogDb].[dbo].[ChangeLog]
(
[Changelog_ID] [int] IDENTITY(1,1) NOT NULL,
[Event Type] [nvarchar](30) NOT NULL,
[When] [datetime] NOT NULL CONSTRAINT [DF_ChangeLog_When]  DEFAULT (getdate()),
[Database] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](128) NOT NULL,
[SPID] [nvarchar](128) NOT NULL,
[Schema] [nvarchar](128) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[New Name] [nvarchar](128) NOT NULL,
[ObjectType] [nvarchar](128) NOT NULL,
[New ObjectType] [nvarchar](128) NOT NULL,
[SQL Used] [nvarchar](max) NOT NULL,
[NTUserName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_NTUser]  DEFAULT (suser_sname()),
[HostName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_HostName]  DEFAULT (host_name()),
[SessionLoginName] [nvarchar](128) NOT NULL CONSTRAINT [DF_ChangeLog_SessionLoginName]  DEFAULT (suser_name()),
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED ([Changelog_ID] ASC)
)

AFTER INSERT AS 
BEGIN
DECLARE @sqlbatch NVARCHAR(MAX)
DECLARE @databasename VARCHAR(255)
DECLARE sqlused_cursor CURSOR FAST_FORWARD FOR
SELECT i.[DATABASE], i.[SQL Used] FROM inserted i WHERE i.[Database] = 'TheDevDatabase'        
OPEN sqlused_cursor
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
WHILE @@FETCH_STATUS = 0
BEGIN
-- re-execute in the context of the TheSchemaDatabase db.
EXEC TheSchemaDatabase.dbo.sp_executesql @[email protected]
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
END
CLOSE sqlused_cursor
DEALLOCATE sqlused_cursor
END
JJS
JJS
September 12, 2016 16:31 PM

Related Questions


Purpose of owning a schema in SQL Server

Updated July 01, 2015 13:02 PM

Deny access to information schema in SQL Server

Updated May 07, 2018 14:06 PM


Database Table Schema Circular Reference issue

Updated February 24, 2017 21:06 PM