作者：爱蜜儿小秋秋 | 来源：互联网 | 2018-07-10 11:32
孤立用户概念所谓孤立用户即指在服务器实例上未定义或错误定义了其相应SQLServer登录名的数据库用户无法登录到实例。这样的用户被称为此服务器实例上的数据库的“孤立用户”。如果删除了对应的SQLServer登录名，则数据库用户可能会变为孤立用户。
孤立用户概念 所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。 这样的用户被称为此服务器实例上的数据库的“孤立用户”。 如果删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。
孤立用户概念
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160; 所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。 这样的用户被称为此服务器实例上的数据库的“孤立用户”。 如果删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。 另外，在数据库还原或附加到 SQL Server 的其他实例之后，数据库用户也可能变为孤立用户。 如果未在新服务器实例中提供数据库用户映射到的 SID，则该用户可能变为孤立用户
检测孤立用户
检测孤立用户相当简单，可以使用下面SQL语句
Code Snippet
当然如果你不想用系统自带的存储过程sp_change_users_login，其实检测孤立账号也很简单，一个简单的SQL语句即可搞定：
Code Snippet
从上面可以看出，
&＃160;&＃160;&＃160; 1：孤立账号必须是SQL Server 用户（issqluser= 1）,:
&＃160;&＃160;&＃160; 2：它必须是sys、guest、INFORMATION_SCHEMA账号以外的SQL Server用户
&＃160;&＃160;&＃160; SELECT * FROM sysusers WHERE SID IS NULL OR SID = 0x0;
3：它返回与安全标识号 (SID) 关联的登录名必须为空值
4：SID的长度小于16
解决孤立账号
方法1:
1: Step 1: 检测、查看对应的孤立账号
2:&＃160;
3:&＃160;
4: USE ;
5:&＃160;
6: GO
7:&＃160;
8: EXEC sp_change_users_login @Action='Report';
9:&＃160;
10: GO
11:&＃160;
12: Step 2: 新建对应的登录名，例如上面检测到Test账号为孤立账号
13:&＃160;
14: USE [master]
15:&＃160;
16: GO
17:&＃160;
18: CREATE LOGIN [Test] WITH PASSWORD=N'Pa@#456' MUST_CHANGE, DEFAULT_DATABASE=[xxxx], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
19:&＃160;
20: GO
21:&＃160;
22: Step 3:
23:&＃160;
24: USE EASN_EAP;
25:&＃160;
26: GO
27:&＃160;
28: EXEC sp_change_users_login @Action='Update_one',@UserNamePattern='xxxx',@LoginName='xxxx';
29:&＃160;
30: Step 4: 重复执行Step 1、Step 2、Step 3解决其它孤立账号，，直到所有孤立账号全部被Fix掉。
31:&＃160;
方法2：对于方法1，如果账号比较多，操作起来比较郁闷，重复干繁琐的体力活。于是我写了一个存储过程来解决
1: SET ANSI_NULLS ON
2: GO
3:&＃160;
4: SET QUOTED_IDENTIFIER ON
5: GO
6:&＃160;
7:&＃160;
8:&＃160;
( 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:&＃160;
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:&＃160;
[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:&＃160;
44: DECLARE @UserName NVARCHAR(64);
45: DECLARE @tmpPwd
VARCHAR(20);
46: DECLARE @LoginRows INT;
47: DECLARE @PwdRows INT;
48:&＃160;
49:
50:
52: BEGIN
53:
RAISERROR('%s Invalid. Please check the paramter %s value',16,1, '@DefaultPwd');
54:
RETURN 1;
55: END
56:&＃160;
@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:&＃160;
#TempLoginNams
67:
(
68:
ID
INT,
69:
UserName
VARCHAR(20),
70:
)
71:&＃160;
72:
INSERT INTO #TempLoginNams
73:
( ID, UserName )
74:
SELECT * FROM dbo.SplitString(@LoginName,'|');
75:&＃160;
#TempPassword
77:
(
78:
ID
INT,
79:
UserPassrd VARCHAR(20)
80:
)
81:&＃160;
82:
INSERT INTO #TempPassword
83:
SELECT * FROM dbo.SplitString(@Password,'|');
84:&＃160;
85:
SELECT @LoginRows=COUNT(1) FROM #TempLoginNams;
86:
SELECT @PwdRows=COUNT(10) FROM #TempPassword;
87:&＃160;
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:&＃160;
94: END
95:&＃160;
96:&＃160;
#OrphanedUser
98: (
99:
UserName sysname,
100:
UserId
INT
101: )
102:&＃160;
103:&＃160;
104: INSERT INTO #OrphanedUser EXEC sp_change_users_login @Action='Report';
105:&＃160;
106:&＃160;
108:
SELECT UserName FROM #OrphanedUser;
109:
110:&＃160;
111: OPEN Cur_OrphanedUsers;
112:&＃160;
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:&＃160;
#OrphanedUser;
147:&＃160;
148: IF @IsUseSamePwd = 0
149: BEGIN
#TempLoginNams;
#TempPassword;
152: END
153:&＃160;
154: GO
&＃160;
其中该存储过程调用了一个Function成为SplitString，该函数是我从网上搜索得来的，作者不详，本来想自己重写该函数，后来觉得没有必要重复造轮子。因为这个函数完全满足我的需求。
&＃160;
Code Snippet
&＃160;
这个存储过程在执行时，有一个既可以说是小bug，也可以说没有验证的错误，就是登录名的密码设置如果过于简单，则执行
EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL,&＃160;&＃160; @tmpPwd; 则会报如下错误
![[译]技术公司十年经验的职场生涯回顾](https://img8.php1.cn/3cdc5/24912/711/b6574f3292f9dc00.png)
京公网安备 11010802041100号