Sql-server – how to determine why I cannot drop a schema

ddlschemasql serversql-server-2008

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.

Best Answer

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 @Stmt=@sqlbatch
FETCH NEXT FROM sqlused_cursor INTO @databasename, @sqlbatch
END
CLOSE sqlused_cursor
DEALLOCATE sqlused_cursor
END

Related Solutions

Sql-server – Alter failed for schema ‘dbo’ when trying to change schema owner

You can't change the owner of "dbo" because it is a system schema. From ALTER AUTHORIZATION, "Special Cases and Conditions":

SCHEMA
... Cannot change the owner of sys, dbo, or information_schema

And you can't set a default schema for windows logons (yet).

This matters in 2 ways:

you are issuing DDL (CREATE TABLE) etc when you want objects to be in dbo
you won't have reusable query plans for DML without schema so different users will have different query plans

Basically, you have two options:

fix the queries
stop using ODBCDirect from MS Access to SQL Server.

The third option, ugly and dangerous, is to make the Windows logon "sysadmin" which means they will always map to dbo. I don't recommend this

Personally, I don't use the dbo schema any more: see dba.se (and the further links there)

Sql-server – Allow user to do anything within his own schema but not create or drop the schema itself

There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).

The required permission to CREATE ALTER and DROP other objects is the CREATE permission for the object type (table\procedure\function\view) combined with ALTER permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL permission. For reference, here is a BOL list of DDL statements where You can find the required permission for all object types.

For the lazy, here is your code after removing the unnecessary permission:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

Best Answer

Related Solutions

Sql-server – Alter failed for schema ‘dbo’ when trying to change schema owner

Sql-server – Allow user to do anything within his own schema but not create or drop the schema itself

Related Question