代码改变世界

SQL Server解决孤立用户浅析

  潇湘隐者  阅读(6389)  评论(9编辑  收藏  举报

孤立用户概念

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

检测孤立用户

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

Code Snippet
  1. USE DatabaseName;
  2. GO
  3.  
  4. EXEC sp_change_users_login @Action = 'Report';
  5. GO

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

Code Snippet
  1. SELECT   UserName = name ,
  2.          UserSID = sid
  3. FROM     sysusers
  4. WHERE    issqluser = 1
  5.          AND ( sid IS NOT NULL
  6.                AND sid <> 0x0
  7.              )
  8.          AND ( LEN(sid) <= 16 )
  9.          AND SUSER_SNAME(sid) IS NULL
  10. 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;

clip_image001

3:它返回与安全标识号 (SID) 关联的登录名必须为空值

4:SID的长度小于16

解决孤立账号

方法1:

   1: Step 1:  检测、查看对应的孤立账号
   2:  
   3:  
   4: USE <DatabaseName>;
   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:  
   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
  1. CREATE FUNCTION SplitString
  2.     (
  3.       -- Add the parameters for the function here
  4.       @myString VARCHAR(500) ,
  5.       @deliminator VARCHAR(10)
  6.     )
  7. RETURNS @ReturnTable TABLE
  8.     (
  9.       -- Add the column definitions for the TABLE variable here
  10.       [id] [int] IDENTITY(1, 1)
  11.                  NOT NULL ,
  12.       [part] [varchar](50) NULL
  13.     )
  14. AS
  15.     BEGIN
  16.         DECLARE @iSpaces INT
  17.         DECLARE @part VARCHAR(50)
  18.         --initialize spaces 
  19.         SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0)
  20.         WHILE @iSpaces > 0
  21.             BEGIN
  22.                 SELECT  @part = SUBSTRING(@myString, 0,
  23.                                           CHARINDEX(@deliminator, @myString, 0))
  24.                 INSERT  INTO @ReturnTable
  25.                         ( part )
  26.                         SELECT  @part
  27.                 SELECT  @myString = SUBSTRING(@mystring,
  28.                                               CHARINDEX(@deliminator,
  29.                                                         @myString, 0)
  30.                                               + LEN(@deliminator),
  31.                                               LEN(@myString) - CHARINDEX(' ',
  32.                                                               @myString, 0))
  33.                 SELECT  @iSpaces = CHARINDEX(@deliminator, @myString, 0)
  34.             END
  35.         IF LEN(@myString) > 0
  36.             INSERT  INTO @ReturnTable
  37.                     SELECT  @myString
  38.         RETURN
  39.     END
  40.            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
  1. CREATE PROCEDURE [dbo].[sp_fix_orphaned_users]
  2. AS
  3.     BEGIN
  4.         DECLARE @UserName NVARCHAR(64)
  5.         CREATE TABLE #SqlLoginUser
  6.         (
  7.             UserName SYSNAME ,
  8.             UserId INT IDENTITY(1, 1)
  9.         )
  10.          
  11.         INSERT  INTO #SqlLoginUser( UserName ) SELECT  [name]  FROM  SYS.[sql_logins]
  12.         CREATE TABLE #OrphanedUser
  13.             (
  14.               UserName SYSNAME ,
  15.               UserId INT
  16.             )
  17.         INSERT  INTO #OrphanedUser EXEC sp_change_users_login @Action = 'Report';
  18.         DECLARE Cur_OrphanedUsers CURSOR
  19.         FOR
  20.             SELECT  UserName
  21.             FROM    #OrphanedUser;
  22.         OPEN Cur_OrphanedUsers;
  23.         FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName;
  24.         WHILE ( @@FETCH_STATUS = 0 )
  25.             BEGIN
  26.                 IF ( @UserName IN ( SELECT  [UserName]
  27.                                     FROM    [#SqlLoginUser] ) )
  28.                     BEGIN
  29.                         EXEC sp_change_users_login @Action = 'update_one',
  30.                             @UserNamePattern = @UserName,
  31.                             @LoginName = @UserName;
  32.                     END
  33.                 ELSE
  34.                     BEGIN
  35.                         DECLARE @SQL NVARCHAR(200)
  36.                         SET @SQL = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD='''''
  37.                         EXEC(@SQL)
  38.                         EXEC sp_change_users_login @Action = 'update_one',
  39.                             @UserNamePattern = @UserName,
  40.                             @LoginName = @UserName;
  41.                     END
  42.    
  43.                 FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName
  44.             END
  45.         CLOSE Cur_OrphanedUsers
  46.         DEALLOCATE Cur_OrphanedUsers
  47.      
  48.         DROP TABLE   #OrphanedUser
  49.         DROP TABLE  #SqlLoginUser
  50.     END
  51. 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
  1. USE [master]
  2. GO
  3. CREATE LOGIN [test] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  4. 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掉。

点击右上角即可分享
微信分享提示