SQLServer解決孤立用戶淺析

孤立用戶概念 所謂孤立用戶即指在服務器實例上未定義或錯誤定義了其相應 SQL Server 登錄名的數據庫用戶無法登錄到實例。 這樣的用戶被稱為此服務器實例上的數據庫的“孤立用戶”。 如果刪除了對應的 SQL Server 登錄名，則數據庫用戶可能會變為孤立用戶。

孤立用戶概念

       所謂孤立用戶即指在服務器實例上未定義或錯誤定義了其相應 SQL Server 登錄名的數據庫用戶無法登錄到實例。 這樣的用戶被稱為此服務器實例上的數據庫的“孤立用戶”。 如果刪除了對應的 SQL Server 登錄名，則數據庫用戶可能會變為孤立用戶。 另外，在數據庫還原或附加到 SQL Server 的其他實例之后，數據庫用戶也可能變為孤立用戶。 如果未在新服務器實例中提供數據庫用戶映射到的 SID，則該用戶可能變為孤立用戶

檢測孤立用戶

檢測孤立用戶相當簡單，可以使用下面SQL語句

Code Snippet

當然如果你不想用系統自帶的存儲過程sp_change_users_login，其實檢測孤立賬號也很簡單，一個簡單的SQL語句即可搞定：

Code Snippet

從上面可以看出，

    1：孤立賬號必須是SQL Server 用戶（issqluser= 1）,:

    2：它必須是sys、guest、INFORMATION_SCHEMA賬號以外的SQL Server用戶

    SELECT * FROM sysusers WHERE SID IS NULL OR SID = 0x0;

3：它返回與安全標識號 (SID) 關聯的登錄名必須為空值

4：SID的長度小于16

解決孤立賬號

方法1:

1: Step 1: 檢測、查看對應的孤立賬號 2:  3:  4: USE ; 5:  6: GO 7:  8: EXEC sp_change_users_login @Action='Report'; 9:  10: GO 11:  12: Step 2: 新建對應的登錄名，例如上面檢測到Test賬號為孤立賬號 13:  14: USE [master] 15:  16: GO 17:  18: CREATE LOGIN [Test] WITH PASSWORD=N'Pa@#456' MUST_CHANGE, DEFAULT_DATABASE=[xxxx], CHECK_EXPIRATION=ON, CHECK_POLICY=ON 19:  20: GO 21:  22: Step 3: 23:  24: USE EASN_EAP; 25:  26: GO 27:  28: EXEC sp_change_users_login @Action='Update_one',@UserNamePattern='xxxx',@LoginName='xxxx'; 29:  30: Step 4: 重復執行Step 1、Step 2、Step 3解決其它孤立賬號，，直到所有孤立賬號全部被Fix掉。 31: 

方法2：對于方法1，如果賬號比較多，操作起來比較郁悶，重復干繁瑣的體力活。于是我寫了一個存儲過程來解決

1: SET ANSI_NULLS ON 2: GO 3:  4: SET QUOTED_IDENTIFIER ON 5: GO 6:  7:  8:  ( SELECT 1 10: FROM dbo.sysobjects 11: WHERE id = OBJECT_ID(N'sp_fix_orphaned_users') 12: AND OBJECTPROPERTY(id, 'IsProcedure') = 1 ) sp_fix_orphaned_users; 14: GO 15:  16: --================================================================================ 17: -- ProcedureName : sp_fix_orphaned_users 18: -- Author : Kerry 19: -- CreateDate : 2013-12-08 20: -- Description : 批量解決數據庫孤立賬號 21: -- 22: /********************************************************************************************** 23: Parameters : 參數說明 24: *********************************************************************************************** 25: @DefaultPwd : 所有孤立賬戶使用同一個密碼@DefaultPwd 26: @LoginName : 所有需要fix的孤立賬戶，eg 'test1|test2|test3' 表示孤立賬戶test1、test2、test3。 27: @Password : 對應@LoginName，eg '@341|Dbd123|D#25' 分別表示上面賬號對應的密碼 28: ************************************************************************************************* 29: Modified Date Modified User Version Modified Reason 30: ************************************************************************************************** 2013-12-08 Kerry V01.00.00 創建該存儲過程。 31: 32: *************************************************************************************************/ 33: --================================================================================================= 34:  [dbo].[sp_fix_orphaned_users] 36: ( 37: @IsUseSamePwd INT = 0 , 38: @DefaultPwd VARCHAR(32) = NULL , 39: @LoginName NVARCHAR(MAX) =NULL, 40: @Password NVARCHAR(MAX) =NULL 41: ) 42: AS 43:  44: DECLARE @UserName NVARCHAR(64); 45: DECLARE @tmpPwd VARCHAR(20); 46: DECLARE @LoginRows INT; 47: DECLARE @PwdRows INT; 48:  49: 50: 52: BEGIN 53: RAISERROR('%s Invalid. Please check the paramter %s value',16,1, '@DefaultPwd'); 54: RETURN 1; 55: END 56:  @Password IS NULL) 58: BEGIN 59: RAISERROR('%s Invalid. Please check the paramter %s value',16,1, '@Password'); 60: RETURN 1; 61: END 62: 63: IF @IsUseSamePwd = 0 64: BEGIN 65:  #TempLoginNams 67: ( 68: ID INT, 69: UserName VARCHAR(20), 70: ) 71:  72: INSERT INTO #TempLoginNams 73: ( ID, UserName ) 74: SELECT * FROM dbo.SplitString(@LoginName,'|'); 75:  #TempPassword 77: ( 78: ID INT, 79: UserPassrd VARCHAR(20) 80: ) 81:  82: INSERT INTO #TempPassword 83: SELECT * FROM dbo.SplitString(@Password,'|'); 84:  85: SELECT @LoginRows=COUNT(1) FROM #TempLoginNams; 86: SELECT @PwdRows=COUNT(10) FROM #TempPassword; 87:  88: IF @LoginRows != @PwdRows 89: BEGIN 90: RAISERROR('The paramter %s have different nums. Please check the paramter %s value',16,1, '@LoginName & @Password '); 91: RETURN 1; 92: END 93:  94: END 95:  96:  #OrphanedUser 98: ( 99: UserName sysname, 100: UserId INT 101: ) 102:  103:  104: INSERT INTO #OrphanedUser EXEC sp_change_users_login @Action='Report'; 105:  106:  108: SELECT UserName FROM #OrphanedUser; 109: 110:  111: OPEN Cur_OrphanedUsers; 112:  Cur_OrphanedUsers INTO @UserName; 114: WHILE ( @@FETCH_STATUS = 0 ) 115: BEGIN 116: IF @IsUseSamePwd = 1 117: BEGIN 118: 119: EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL, 120: @DefaultPwd; 121: 122: 123: EXEC sp_change_users_login @Action = 'update_one', 124: @UserNamePattern = @UserName, @LoginName = @UserName; 125: END 126: ELSE 127: BEGIN 128: SELECT @UserName = o.UserName , 129: @tmpPwd = p.UserPassrd 130: FROM #OrphanedUser o #TempLoginNams l ON o.UserName = l.UserName #TempPassword p ON l.ID = p.ID 133: WHERE o.UserName = @UserName; 134: 135: EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL, 136: @tmpPwd; 137: EXEC sp_change_users_login @Action = 'update_one', 138: @UserNamePattern = @UserName, @LoginName = @UserName; 139: END 140: Cur_OrphanedUsers INTO @UserName 142: END 143: CLOSE Cur_OrphanedUsers 144: DEALLOCATE Cur_OrphanedUsers 145:  #OrphanedUser; 147:  148: IF @IsUseSamePwd = 0 149: BEGIN #TempLoginNams; #TempPassword; 152: END 153:  154: GO

其中該存儲過程調用了一個Function成為SplitString，該函數是我從網上搜索得來的，作者不詳，本來想自己重寫該函數，后來覺得沒有必要重復造輪子。因為這個函數完全滿足我的需求。

Code Snippet

這個存儲過程在執行時，有一個既可以說是小bug，也可以說沒有驗證的錯誤，就是登錄名的密碼設置如果過于簡單，則執行

EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL,   @tmpPwd; 則會報如下錯誤

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com