如何在SQL Server中删除数据库架构和用户

I have a sql code to drop the schema and the user, see below:

我有一个sql代码来删除架构和用户，见下文：

 USE [DB1]
GO
--SELECT * FROM sys.schemas s WHERE s.principal_id = USER_ID('ITAdmin')
WHILE EXISTS (SELECT * FROM sys.schemas s WHERE s.principal_id = USER_ID('ITAdmin'))
BEGIN
    DECLARE @tmpSchemaHolderToBeDroppedOrChanged NVARCHAR(100), @tmpAlterSchemaAuthorisationStatement NVARCHAR(200)
    SET @tmpAlterSchemaAuthorisationStatement = 'DROP SCHEMA '
    SET @tmpSchemaHolderToBeDroppedOrChanged = (SELECT TOP 1 s.name FROM sys.schemas s WHERE s.principal_id = USER_ID('ITAdmin'))
    IF @tmpSchemaHolderToBeDroppedOrChanged = 'ITAdmin'
    BEGIN
            PRINT 'Dropping ITAdmin schema.'
            IF schema_id('ITAdmin') IS NOT NULL
            BEGIN
                    DROP SCHEMA ITAdmin;
                    PRINT 'ITAdmin schema has been dropped.'
            END
    END
    ELSE
    BEGIN
            --Reassign other schemas linked to the ITAdmin user back to themselves.
            SET @tmpAlterSchemaAuthorisationStatement = 'ALTER AUTHORIZATION ON SCHEMA::'

            SET @tmpAlterSchemaAuthorisationStatement = @tmpAlterSchemaAuthorisationStatement + @tmpSchemaHolderToBeDroppedOrChanged + ' TO ' + @tmpSchemaHolderToBeDroppedOrChanged

            PRINT @tmpAlterSchemaAuthorisationStatement
            EXEC sp_executesql  @tmpAlterSchemaAuthorisationStatement
    END
END
GO
if user_id('ITAdmin') is not null
begin
    DROP USER ITAdmin;
end

It is acting like it has deleted the schema and user but its not doing what is supposed to do. How can I fix this code?

它表现得像删除了架构和用户，但它没有做应该做的事情。我该如何修复此代码？

1 个解决方案

#1

I dropped the schema and user using the following scripts:

我使用以下脚本删除了架构和用户：

DROP SCHEMA ITAdmin
DROP USER ITAdmin

I then execute the code, it worked! This is very weird but it worked.

然后我执行代码，它工作了！这很奇怪但是很有效。

#1