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; 则会报如下错误