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:
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
15:
16: GO
17:
18: CREATE LOGIN WITH PASSWORD=N'Pa@#456' MUST_CHANGE, DEFAULT_DATABASE=, 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 ( SELECT1
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: --
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: ********************************************************************************************************************
31: 2013-12-08 Kerry V01.00.00 创建该存储过程。
32:
33: *******************************************************************************************************************/
34: --==================================================================================================================
35:
36: CREATE PROCEDURE .
37: (
38: @IsUseSamePwdINT = 0 ,
39: @DefaultPwd VARCHAR(32) = NULL ,
40: @LoginName NVARCHAR(MAX) =NULL,
41: @Password NVARCHAR(MAX) =NULL
42: )
43: AS
44:
45: DECLARE @UserName NVARCHAR(64);
46: DECLARE @tmpPwd VARCHAR(20);
47: DECLARE @LoginRows INT;
48: DECLARE @PwdRows INT;
49:
50:
51:
52: IF @IsUseSamePwd =1 AND @DefaultPwd IS NULL
53: BEGIN
54: RAISERROR('%s Invalid. Please check the paramter %s value',16,1, '@DefaultPwd');
55: RETURN 1;
56: END
57:
58: IF @IsUseSamePwd = 0 AND ( @LoginName IS NULL OR@Password IS NULL)
59: BEGIN
60: RAISERROR('%s Invalid. Please check the paramter %s value',16,1, '@Password');
61: RETURN 1;
62: END
63:
64: IF @IsUseSamePwd = 0
65: BEGIN
66:
67: CREATE TABLE #TempLoginNams
68: (
69: ID INT,
70: UserName VARCHAR(20),
71: )
72:
73: INSERT INTO #TempLoginNams
74: ( ID, UserName )
75: SELECT * FROM dbo.SplitString(@LoginName,'|');
76:
77: CREATE TABLE #TempPassword
78: (
79: ID INT,
80: UserPassrdVARCHAR(20)
81: )
82:
83: INSERT INTO #TempPassword
84: SELECT * FROM dbo.SplitString(@Password,'|');
85:
86: SELECT @LoginRows=COUNT(1) FROM #TempLoginNams;
87: SELECT @PwdRows=COUNT(10) FROM #TempPassword;
88:
89: IF @LoginRows != @PwdRows
90: BEGIN
91: RAISERROR('The paramter %s have different nums. Please check the paramter %s value',16,1, '@LoginName & @Password ');
92: RETURN 1;
93: END
94:
95: END
96:
97:
98: CREATE TABLE #OrphanedUser
99: (
100: UserName sysname,
101: UserId INT
102: )
103:
104:
105: INSERT INTO #OrphanedUser EXEC sp_change_users_login @Action='Report';
106:
107:
108: DECLARE Cur_OrphanedUsers CURSOR FOR
109: SELECT UserName FROM #OrphanedUser;
110:
111:
112: OPEN Cur_OrphanedUsers;
113:
114: FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName;
115: WHILE ( @@FETCH_STATUS = 0 )
116: BEGIN
117: IF @IsUseSamePwd = 1
118: BEGIN
119:
120: EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL,
121: @DefaultPwd;
122:
123:
124: EXEC sp_change_users_login @Action = 'update_one',
125: @UserNamePattern = @UserName, @LoginName = @UserName;
126: END
127: ELSE
128: BEGIN
129: SELECT@UserName = o.UserName ,
130: @tmpPwd = p.UserPassrd
131: FROM #OrphanedUser o
132: LEFT JOIN #TempLoginNams l ON o.UserName = l.UserName
133: LEFT JOIN #TempPassword p ON l.ID = p.ID
134: WHERE o.UserName = @UserName;
135:
136: EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL,
137: @tmpPwd;
138: EXEC sp_change_users_login @Action = 'update_one',
139: @UserNamePattern = @UserName, @LoginName = @UserName;
140: END
141:
142: FETCH NEXT FROM Cur_OrphanedUsers INTO @UserName
143: END
144: CLOSE Cur_OrphanedUsers
145: DEALLOCATE Cur_OrphanedUsers
146:
147: DROP TABLE#OrphanedUser;
148:
149: IF @IsUseSamePwd = 0
150: BEGIN
151: DROP TABLE #TempLoginNams;
152: DROP TABLE #TempPassword;
153: END
154:
155: 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
IDENTITY(1, 1)
NOT NULL ,
(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))
INSERTINTO @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
INSERTINTO @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: sp_fix_orphaned_users @IsUseSamePwd =0 ,
2: @LoginName ='user1|user2|user3',
3: @Password ='Jk(*45(|Jk(*45(|Jk(*45(';
当青春已成青春，该拿什么去挥霍. 他给她留言。你的，我都懂。我的，你有太多不懂。 蓝天下的我们 那么渺小 那么寂寞｀
页:
[1]