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

时间:2021-11-15 12:48:06

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


0  

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


0  

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.

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