SQL Server解决孤立用户浅析-白红宇

原文:

孤立用户概念

所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。这样的用户被称为此服务器实例上的数据库的“孤立用户”。如果删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。另外，在数据库还原或附加到 SQL Server 的其他实例之后，数据库用户也可能变为孤立用户。如果未在新服务器实例中提供数据库用户映射到的 SID，则该用户可能变为孤立用户

检测孤立用户

检测孤立用户相当简单，可以使用下面SQL语句

       Code Snippet 
 USE DatabaseName; 
 GO 
  
 EXEC sp_change_users_login @Action = 'Report'; 
 GO 

当然如果你不想用系统自带的存储过程sp_change_users_login，其实检测孤立账号也很简单，一个简单的SQL语句即可搞定：

       Code Snippet 
 SELECT   UserName = name , 
         UserSID = sid 
 FROM     sysusers 
 WHERE    issqluser = 1 
         AND ( sid IS NOT NULL 
               AND sid <> 0x0 
             ) 
         AND ( LEN(sid) <= 16 ) 
         AND SUSER_SNAME(sid) IS NULL 
 ORDER BY name 

从上面可以看出，

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:

Step 1:  检测、查看对应的孤立账号
 
 
USE 
       ;
 
GO
 
EXEC sp_change_users_login  @Action='Report';
 
GO
 
Step 2: 新建对应的登录名，例如上面检测到Test账号为孤立账号
 
USE [master]
 
GO
 
CREATE LOGIN [Test] WITH PASSWORD=N'Pa@#456' MUST_CHANGE, DEFAULT_DATABASE=[xxxx], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
 
GO
 
Step 3:
 
USE EASN_EAP;
 
GO
 
EXEC sp_change_users_login @Action='Update_one',@UserNamePattern='xxxx',@LoginName='xxxx';
 
Step 4: 重复执行Step 1、Step 2、Step 3解决其它孤立账号，直到所有孤立账号全部被Fix掉。
 

方法2：对于方法1，如果账号比较多，操作起来比较郁闷，重复干繁琐的体力活。于是我写了一个存储过程来解决

1: SET ANSI_NULLS ON

2: GO

3:

4: SET QUOTED_IDENTIFIER ON

5: GO

6:

7:

8:

9: IF EXISTS ( SELECT  1

10:             FROM    dbo.sysobjects

11:             WHERE   id = OBJECT_ID(N'sp_fix_orphaned_users')

12:                     AND OBJECTPROPERTY(id, 'IsProcedure') = 1 )

13:     DROP PROCEDURE 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: --                                        http://www.cnblogs.com/kerrycode/

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:

35: CREATE PROCEDURE [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:

51: IF @IsUseSamePwd =1 AND @DefaultPwd IS NULL

52: BEGIN

53:     RAISERROR('%s Invalid. Please check the paramter %s value',16,1, '@DefaultPwd');

54:     RETURN 1;

55: END

56:

57: IF @IsUseSamePwd = 0 AND ( @LoginName IS NULL OR  @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:

66:     CREATE TABLE #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:

76:     CREATE TABLE #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:

97: CREATE TABLE #OrphanedUser

98: (

99:     UserName    sysname,

100:     UserId        INT

101: )

102:

103:

104: INSERT INTO #OrphanedUser EXEC sp_change_users_login @Action='Report';

105:

106:

107: DECLARE Cur_OrphanedUsers CURSOR FOR

108:     SELECT UserName FROM #OrphanedUser;

109:

110:

111: OPEN Cur_OrphanedUsers;

112:

113: FETCH NEXT FROM 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

131:                         LEFT JOIN #TempLoginNams l ON o.UserName = l.UserName

132:                         LEFT JOIN #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:

141:         FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName

142:     END

143: CLOSE Cur_OrphanedUsers

144: DEALLOCATE Cur_OrphanedUsers

145:

146: DROP TABLE  #OrphanedUser;

147:

148: IF @IsUseSamePwd = 0

149: BEGIN

150:     DROP TABLE #TempLoginNams;

151:     DROP TABLE #TempPassword;

152: END

153:

154: GO

其中该存储过程调用了一个Function成为SplitString，该函数是我从网上搜索得来的，作者不详，本来想自己重写该函数，后来觉得没有必要重复造轮子。因为这个函数完全满足我的需求。

       Code Snippet 
 CREATE FUNCTION SplitString 
    ( 
      -- Add the parameters for the function here  
      @myString VARCHAR(500) , 
      @deliminator VARCHAR(10) 
    ) 
 RETURNS @ReturnTable TABLE 
    ( 
      -- Add the column definitions for the TABLE variable here  
      [id] [int] IDENTITY(1, 1) 
                 NOT NULL , 
      [part] [varchar](50) NULL 
    ) 
 AS 
    BEGIN 
        DECLARE @iSpaces INT 
        DECLARE @part VARCHAR(50) 
        --initialize spaces   
        SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0) 
        WHILE @iSpaces > 0 
            BEGIN 
                SELECT  @part = SUBSTRING(@myString, 0, 
                                          CHARINDEX(@deliminator, @myString, 0)) 
                INSERT  INTO @ReturnTable 
                        ( part ) 
                        SELECT  @part 
                SELECT  @myString = SUBSTRING(@mystring, 
                                              CHARINDEX(@deliminator, 
                                                        @myString, 0) 
                                              + LEN(@deliminator), 
                                              LEN(@myString) - CHARINDEX(' ', 
                                                              @myString, 0)) 
                SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0) 
            END 
        IF LEN(@myString) > 0 
            INSERT  INTO @ReturnTable 
                    SELECT  @myString 
        RETURN 
    END 
           GO 

这个存储过程在执行时，有一个既可以说是小bug，也可以说没有验证的错误，就是登录名的密码设置如果过于简单，则执行

EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL, @tmpPwd; 则会报如下错误

消息 15118，级别 16，状态 1，第 1 行

密码有效性验证失败。该密码不够复杂，不符合 Windows 策略要求。

消息 15497，级别 16，状态 1，过程 sp_change_users_login，第 223 行

无法使用 sp_addlogin 添加登录名(用户 = easn)。即将终止此过程。

一时还没有找到如何去验证密码是否符合复杂度的方法，留待以后进一步完善。

假如迁移数据库后，发现有user1、user2、user3三个孤立账号，如果我想着三个孤立账号使用同一密码，那么执行SQL 1 ，如果我想给user1、user2、user3三个账号设置各自密码，那么使用SQL 2解决孤立账号问题。

1: --SQL 1

2: EXEC [dbo].[sp_fix_orphaned_users] @IsuseSamePwd =1,@DefaultPwd='Qwe!@423'

3:

4: --SQL 2

5: EXEC [dbo].[sp_fix_orphaned_users] @IsuseSamePwd =0, @loginName='user1|user2|user3', @Password='Qwe!@423|QweD2@#4|Oi87^%'

看到桦仔的回复（修改后的存储过程后），那个确实是个不错的方法，我测试了一下后发现还是这个问题：

       Code Snippet 
 CREATE PROCEDURE [dbo].[sp_fix_orphaned_users] 
AS
    BEGIN 
        DECLARE @UserName NVARCHAR(64) 
 
        CREATE TABLE #SqlLoginUser 
        ( 
            UserName SYSNAME , 
            UserId INT IDENTITY(1, 1) 
        ) 
         
        INSERT  INTO #SqlLoginUser( UserName ) SELECT  [name]  FROM  SYS.[sql_logins] 
 
 
        CREATE TABLE #OrphanedUser 
            ( 
              UserName SYSNAME , 
              UserId INT 
            ) 
 
        INSERT  INTO #OrphanedUser EXEC sp_change_users_login @Action = 'Report'; 
 
        DECLARE Cur_OrphanedUsers CURSOR 
        FOR 
            SELECT  UserName 
            FROM    #OrphanedUser; 
        OPEN Cur_OrphanedUsers; 
        FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName; 
        WHILE ( @@FETCH_STATUS = 0 ) 
            BEGIN 
                IF ( @UserName IN ( SELECT  [UserName] 
                                    FROM    [#SqlLoginUser] ) ) 
                    BEGIN 
                        EXEC sp_change_users_login @Action = 'update_one', 
                            @UserNamePattern = @UserName, 
                            @LoginName = @UserName; 
                    END 
                ELSE 
                    BEGIN 
                        DECLARE @SQL NVARCHAR(200) 
                        SET @SQL = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD=''''' 
                        EXEC(@SQL) 
                        EXEC sp_change_users_login @Action = 'update_one', 
                            @UserNamePattern = @UserName, 
                            @LoginName = @UserName; 
                    END 
   
                FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName 
            END 
        CLOSE Cur_OrphanedUsers 
        DEALLOCATE Cur_OrphanedUsers 
     
        DROP TABLE   #OrphanedUser 
        DROP TABLE  #SqlLoginUser 
    END 
 
 EXEC sp_fix_orphaned_users 

消息 15116，级别 16，状态 1，第 1 行

密码有效性验证失败。该密码太短，不符合 Windows 策略要求。

消息 15291，级别 16，状态 1，过程 sp_change_users_login，第 137 行

正在终止此过程。缺少 Login 名称 'xxx' 或该名称无效。

不过对于这个错误倒是很好解决，创建登录名是将CHECK_POLICY设置为OFF，就可避免上面错误。

       Code Snippet 
 USE [master] 
GO
 CREATE LOGIN [test] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO

给登录名密码设置为空，这个做法相当不安全，我还是觉得有所不妥，其实，我现在在的需求是这样（很多时候，由于表达能力不足，没有阐述清楚）：数据库从一台服务器迁移到另外一台服务器后，这个数据库对应的账号变成了孤立账号，假设其孤立账号为U1、U2……UN在迁移整理过程我发现，其实我只需要账号U1、U2、 U4、U6，其它账号没有必要也迁移过去。所以我才为存储过程sp_fix_orphaned_users设置了参数@LoginName和@Password，用于解决这种需求。@LoginName=‘U1|U2|U4|U6’， @Password=‘Pwd1|Pwd2|Pwd4|Pwd6’，而有时候在测试数据库环境，为了图方便、省事，就所有孤立账号使用同一个秘密，这就是加入参数@IsUseSamePwd的缘故。当然这些是我自己的特殊需求。至于如果不用验证密码复杂性，可以结合桦仔的方法，先新建登录名，然后使用sp_change_users_login来Fix掉。