萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 無法刪除使用者/結構描述?2000/2005/通用腳本強制清理解決方案

錯誤:「選定的使用者擁有物件,無法移除」或「資料庫主體在該資料庫中擁有結構描述,無法刪除」。根因:使用者擁有結構描述或物件,必須先轉移擁有權或刪除相依物件。

SQL Server 2005/2008+ 圖形介面解法

  1. SSMS → 資料庫 → 安全性 → 結構描述:刪除該使用者擁有的結構描述(先確認無物件或已轉移)。
  2. → 使用者:刪除使用者。
  3. 若物件擁有者為該使用者:表/視圖/SP 右鍵 → 設計 → 結構描述改為 dbo。
  4. 角色成員資格:「安全性」→「角色」→「資料庫角色」→ 各角色屬性 → 移除該使用者。

SQL Server 2000 系統表直接刪除(高風險,慎用)

  1. 企業管理器 → 伺服器屬性 → 「允許直接修改系統目錄」勾選。
  2. 展開資料庫 → 系統表 sysusers → 開啟資料表 → 刪除目標使用者列。
  3. 回使用者清單刪除使用者 → 取消「允許直接修改系統目錄」。

警告:直接修改系統表可能導致資料庫不一致,僅限 2000 緊急救援,且事前必須完整備份。

通用 T-SQL 腳本:將非 dbo 物件擁有權轉移至 dbo

USE [YourDatabase];
GO

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'EXEC sp_changeobjectowner '
    + QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name)
    + N', ''dbo'';' + CHAR(13)
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name <> 'dbo'
  AND o.type IN ('U','V','P','FN','IF','TF','TR'); -- 表、視圖、SP、函數、觸發器

PRINT @sql; -- 先檢查
-- EXEC sp_executesql @sql; -- 確認無誤後執行

進階:含擴充屬性、權限、同義詞的完整清理腳本

USE [YourDatabase];
GO
BEGIN TRY
    BEGIN TRANSACTION;

    -- 1. 移除擴充屬性
    EXEC sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'OldSchema';

    -- 2. 轉移物件結構描述
    DECLARE @obj SYSNAME, @sch SYSNAME;
    DECLARE cur CURSOR FOR
        SELECT o.name, s.name
        FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE s.name = 'OldSchema' AND o.type IN ('U','V','P','FN','IF','TF','TR');
    OPEN cur;
    FETCH NEXT FROM cur INTO @obj, @sch;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(N'ALTER SCHEMA [dbo] TRANSFER [' + @sch + N'].[' + @obj + N'];');
        FETCH NEXT FROM cur INTO @obj, @sch;
    END
    CLOSE cur; DEALLOCATE cur;

    -- 3. 移除角色成員
    EXEC sp_droprolemember 'db_owner', 'OldUser'; -- 視角色調整

    -- 4. 刪除結構描述
    DROP SCHEMA [OldSchema];

    -- 5. 刪除使用者
    DROP USER [OldUser];

    COMMIT TRANSACTION;
    PRINT '清理完成';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '錯誤: ' + ERROR_MESSAGE();
END CATCH

最佳實務

  • 日常開發統一使用 dbo 結構描述,避免多結構描述管理複雜度。
  • 部署腳本中明確指定 SCHEMA(CREATE TABLE dbo.T(...)),防止預設結構描述漂移。
  • 定期稽核 sys.schemas、sys.database_principals,清理孤立使用者/結構描述。

依循「轉移擁有權 → 移除相依 → 刪除主體」順序,即可安全解決無法刪除使用者的困擾。

copyright © 萬盛學電腦網 all rights reserved