SQL Server解决孤立用户浅析

孤立用户概念 所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。 这样的用户被称为此服务器实例上的数据库的“孤立用户”。 如果删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。

GPT-MINUS1

通过在文本中随机地用同义词替换单词来愚弄GPT

83

查看详情

孤立用户概念

       所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 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; 则会报如下错误