SQL Server解决孤立用户浅析

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

检测孤立用户

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

 18: CREATE LOGIN [Test] WITH PASSWORD=NPa@#456 MUST_CHANGE, DEFAULT_DATABASE=[xxxx], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

 28: EXEC sp_change_users_login @Action=Update_one,@UserNamePattern=xxxx,@LoginName=xxxx;

 30: Step 4: 重复执行Step 1、Step 2、Step 3解决其它孤立账号，直到所有孤立账号全部被Fix掉。

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

 16: --================================================================================

 22: /**********************************************************************************************

 24: ***********************************************************************************************

 26: @LoginName : 所有需要fix的孤立账户，eg test1|test2|test3 表示孤立账户test1、test2、test3。

 27: @Password : 对应@LoginName，eg @341|Dbd123|D#25 分别表示上面账号对应的密码

 28: *************************************************************************************************

 30: ************************************************************************************************** 2013-12-08 Kerry V01.00.00 创建该存储过程。

 32: *************************************************************************************************/

 33: --=================================================================================================

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

 59: RAISERROR(%s Invalid. Please check the paramter %s value,16,1, @Password);

 90: RAISERROR(The paramter %s have different nums. Please check the paramter %s value,16,1, @LoginName @Password );

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

其中该存储过程调用了一个Function成为SplitString，该函数是我从网上搜索得来的，作者不详，本来想自己重写该函数，后来觉得没有必要重复造轮子。因为这个函数完全满足我的需求。
        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解决孤立账号问题。

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

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

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

                  INSERT  INTO #SqlLoginUser( UserName ) SELECT  [name]  FROM  SYS.[sql_logins]

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

CREATE LOGIN [test] WITH PASSWORD=N, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

给登录名密码设置为空，这个做法相当不安全，我还是觉得有所不妥，其实，我现在在的需求是这样（很多时候，由于表达能力不足，没有阐述清楚）： 数据库从一台服务器迁移到另外一台服务器后，这个数据库对应的账号变成了孤立账号，假设其孤立账号为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掉。

[ SQL Server ] 计算N个月前，N个月后的方法 #1. 计算N个月前的日期 #2. 计算N个月后的日期 #3. 月末最后一天的情况（计算从日数多的月到日数较少的月） #4. 月末最后一天的情况（计算从日数少的月到日数较多的月）
【SQL Server】数据库开发指南（三）面向数据分析的 T-SQL 编程技巧与实践 T-SQL 指的是 Transact-SQL，是一种针对 Microsoft SQL Server 数据库系统的 SQL 方言。T-SQL 扩展了标准 SQL 语言，提供了更多的功能和特性，包括事务处理、错误处理、游标处理、动态 SQL、存储过程、触发器、用户定义函数等等。
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载