SQLServer解決孤立用戶淺析

孤立用戶概念 所謂孤立用戶即指在服務(wù)器實(shí)例上未定義或錯(cuò)誤定義了其相應(yīng) SQL Server 登錄名的數(shù)據(jù)庫(kù)用戶無(wú)法登錄到實(shí)例。 這樣的用戶被稱為此服務(wù)器實(shí)例上的數(shù)據(jù)庫(kù)的“孤立用戶”。 如果刪除了對(duì)應(yīng)的 SQL Server 登錄名，則數(shù)據(jù)庫(kù)用戶可能會(huì)變?yōu)楣铝⒂脩簟?/p>

孤立用戶概念

       所謂孤立用戶即指在服務(wù)器實(shí)例上未定義或錯(cuò)誤定義了其相應(yīng) SQL Server 登錄名的數(shù)據(jù)庫(kù)用戶無(wú)法登錄到實(shí)例。 這樣的用戶被稱為此服務(wù)器實(shí)例上的數(shù)據(jù)庫(kù)的“孤立用戶”。 如果刪除了對(duì)應(yīng)的 SQL Server 登錄名，則數(shù)據(jù)庫(kù)用戶可能會(huì)變?yōu)楣铝⒂脩簟?另外，在數(shù)據(jù)庫(kù)還原或附加到 SQL Server 的其他實(shí)例之后，數(shù)據(jù)庫(kù)用戶也可能變?yōu)楣铝⒂脩簟?如果未在新服務(wù)器實(shí)例中提供數(shù)據(jù)庫(kù)用戶映射到的 SID，則該用戶可能變?yōu)楣铝⒂脩?/p>

檢測(cè)孤立用戶

檢測(cè)孤立用戶相當(dāng)簡(jiǎn)單，可以使用下面SQL語(yǔ)句

Code Snippet

當(dāng)然如果你不想用系統(tǒng)自帶的存儲(chǔ)過(guò)程sp_change_users_login，其實(shí)檢測(cè)孤立賬號(hào)也很簡(jiǎn)單，一個(gè)簡(jiǎn)單的SQL語(yǔ)句即可搞定：

Code Snippet

從上面可以看出，

    1：孤立賬號(hào)必須是SQL Server 用戶（issqluser= 1）,:

    2：它必須是sys、guest、INFORMATION_SCHEMA賬號(hào)以外的SQL Server用戶

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

3：它返回與安全標(biāo)識(shí)號(hào) (SID) 關(guān)聯(lián)的登錄名必須為空值

4：SID的長(zhǎng)度小于16

解決孤立賬號(hào)

方法1:

1: Step 1: 檢測(cè)、查看對(duì)應(yīng)的孤立賬號(hào) 2:  3:  4: USE ; 5:  6: GO 7:  8: EXEC sp_change_users_login @Action='Report'; 9:  10: GO 11:  12: Step 2: 新建對(duì)應(yīng)的登錄名，例如上面檢測(cè)到Test賬號(hào)為孤立賬號(hào) 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: 重復(fù)執(zhí)行Step 1、Step 2、Step 3解決其它孤立賬號(hào)，，直到所有孤立賬號(hào)全部被Fix掉。 31: 

方法2：對(duì)于方法1，如果賬號(hào)比較多，操作起來(lái)比較郁悶，重復(fù)干繁瑣的體力活。于是我寫了一個(gè)存儲(chǔ)過(guò)程來(lái)解決

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 : 批量解決數(shù)據(jù)庫(kù)孤立賬號(hào) 21: -- 22: /********************************************************************************************** 23: Parameters : 參數(shù)說(shuō)明 24: *********************************************************************************************** 25: @DefaultPwd : 所有孤立賬戶使用同一個(gè)密碼@DefaultPwd 26: @LoginName : 所有需要fix的孤立賬戶，eg 'test1|test2|test3' 表示孤立賬戶test1、test2、test3。 27: @Password : 對(duì)應(yīng)@LoginName，eg '@341|Dbd123|D#25' 分別表示上面賬號(hào)對(duì)應(yīng)的密碼 28: ************************************************************************************************* 29: Modified Date Modified User Version Modified Reason 30: ************************************************************************************************** 2013-12-08 Kerry V01.00.00 創(chuàng)建該存儲(chǔ)過(guò)程。 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

其中該存儲(chǔ)過(guò)程調(diào)用了一個(gè)Function成為SplitString，該函數(shù)是我從網(wǎng)上搜索得來(lái)的，作者不詳，本來(lái)想自己重寫該函數(shù)，后來(lái)覺(jué)得沒(méi)有必要重復(fù)造輪子。因?yàn)檫@個(gè)函數(shù)完全滿足我的需求。

Code Snippet

這個(gè)存儲(chǔ)過(guò)程在執(zhí)行時(shí)，有一個(gè)既可以說(shuō)是小bug，也可以說(shuō)沒(méi)有驗(yàn)證的錯(cuò)誤，就是登錄名的密碼設(shè)置如果過(guò)于簡(jiǎn)單，則執(zhí)行

EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL,   @tmpPwd; 則會(huì)報(bào)如下錯(cuò)誤

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com