AI 助理
备案控制台
开发者社区 数据库 文章 正文

SQL Server解决孤立用户浅析

2016-04-25 2696
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介：

孤立用户概念

       所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 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)
  6.         CREATE TABLE #SqlLoginUser
  7.         (
  8.             UserName SYSNAME ,
  9.             UserId INT IDENTITY(1, 1)
  10.         )
  11.          
  12.         INSERT  INTO #SqlLoginUser( UserName ) SELECT  [name]  FROM  SYS.[sql_logins]
  15.         CREATE TABLE #OrphanedUser
  16.             (
  17.               UserName SYSNAME ,
  18.               UserId INT
  19.             )
  21.         INSERT  INTO #OrphanedUser EXEC sp_change_users_login @Action = 'Report';
  23.         DECLARE Cur_OrphanedUsers CURSOR
  24.         FOR
  25.             SELECT  UserName
  26.             FROM    #OrphanedUser;
  27.         OPEN Cur_OrphanedUsers;
  28.         FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName;
  29.         WHILE ( @@FETCH_STATUS = 0 )
  30.             BEGIN
  31.                 IF ( @UserName IN ( SELECT  [UserName]
  32.                                     FROM    [#SqlLoginUser] ) )
  33.                     BEGIN
  34.                         EXEC sp_change_users_login @Action = 'update_one',
  35.                             @UserNamePattern = @UserName,
  36.                             @LoginName = @UserName;
  37.                     END
  38.                 ELSE
  39.                     BEGIN
  40.                         DECLARE @SQL NVARCHAR(200)
  41.                         SET @SQL = 'CREATE LOGIN ' + @UserName + ' WITH PASSWORD='''''
  42.                         EXEC(@SQL)
  43.                         EXEC sp_change_users_login @Action = 'update_one',
  44.                             @UserNamePattern = @UserName,
  45.                             @LoginName = @UserName;
  46.                     END
  47.    
  48.                 FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName
  49.             END
  50.         CLOSE Cur_OrphanedUsers
  51.         DEALLOCATE Cur_OrphanedUsers
  52.      
  53.         DROP TABLE   #OrphanedUser
  54.         DROP TABLE  #SqlLoginUser
  55.     END
  57. 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掉。

文章标签：
云数据库 RDS SQL Server 版
SQL
Go
数据安全/隐私保护
数据库
存储
Windows
安全
关键词：
SQL server
SQL用户
SQL server孤立
SQL server孤立用户
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
潇湘隐者
目录
相关文章
游客j4mujezz7vm7y
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
39 0
sunrr
|
5月前
|
SQL 存储 监控
SQL Server的并行实施如何优化？
【7月更文挑战第23天】SQL Server的并行实施如何优化？
sunrr
129 13
小王老师呀
|
5月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能，可使用`generate_series`函数生成整数序列，例如：`SELECT value FROM generate_series(1, 10)。此外，`date_bucket`函数能按指定间隔（如周）对日期时间值分组，这些工具结合窗口函数和其他时间日期函数，能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
小王老师呀
82 9
以山向海
|
5月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
以山向海
69 6
小王老师呀
|
5月前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符（1-8000），适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符，适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好，空间固定。 - VARCHAR(max) 对于大文本更合适，但可能影响性能。 - 选择取决于数据长度预期和业务需求。
小王老师呀
453 1
VipSoft
|
4月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
VipSoft
528 0
小王老师呀
|
5月前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面：检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构（如连接、子查询和索引），服务器资源（CPU、内存、网络延迟），连接和内存设置，以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。
小王老师呀
136 0
小5聊基础
|
存储 SQL 程序员
【Sql Server】存储过程通过作业定时执行按天统计记录
通过前两篇文章的学习，我们已经对创建表、存储过程、作业等功能点有所了解 本次将结合前面所学习的知识点，创建统计表以及结合作业定时按天以及实时统计域名各个长度的记录值
小5聊基础
408 0
【Sql Server】存储过程通过作业定时执行按天统计记录
吃豆子的恐龙
|
存储 SQL 数据库
SQL Server——为什么要使用存储过程？不使用是什么样的？
提高数据库执行速度，可能第一次见到这句话的小伙伴们感觉到非常的匪夷所思叭！怎么就提高了它的执行速度捏，从哪方面可以表现出来呢？既然这里要说到的是为什么要使用存储过程，也就是说它的优点是什么。那我们肯定就要对使用和不使用存储过程两方面来进行对比才能看出它的优点对吧。
吃豆子的恐龙
128 0
授客
|
存储 SQL Go
SQL Server 存储过程
SQL Server 存储过程
授客
165 0

热门文章

最新文章

  • 1
    PostgreSQL SQL HINT的使用
  • 2
    SQL Server 统计信息更新时采样百分比对数据预估准确性的影响
  • 3
    SQL SERVER2005建Link Server
  • 4
    SQL中的保留关键字
  • 5
    【代码分享-SQL】为DB中所有Table增加PK
  • 6
    Sql Server在建好的表中，新增一列
  • 7
    SQL Server CLR 使用 C# 自定义存储过程和触发器
  • 8
    tempdb对SQL Server性能的影响 <转>
  • 9
    SQL SERVER 2012 执行计划走嵌套循环导致性能问题的案例
  • 10
    找到SQL Server数据库历史增长信息
  • 1
    sql语句加正则 简化查询
    33
  • 2
    SQL索引失效原因分析与解决方案
    198
  • 3
    【MySQL实战笔记】02.一条SQL更新语句是如何执行的-2
    54
  • 4
    SQL server 2017安装教程
    93
  • 5
    SQL常见面试题总结2
    105
  • 6
    mysql一条sql查询出多个统计结果
    64
  • 7
    启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
    66
  • 8
    【MySQL】慢SQL分析流程
    107
  • 9
    java.sql.SQLException: No operations allowed after statement closed.
    296
  • 10
    一文搞懂SQL优化——如何高效添加数据
    480

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据表管理
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。