|
|
孤立用户概念
所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 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 [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: --
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 [dbo].[sp_fix_orphaned_users]
37: (
38: @IsUseSamePwd INT = 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: UserPassrd VARCHAR(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
[id] [int] IDENTITY(1, 1)
NOT NULL ,
[part] [varchar](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))
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: sp_fix_orphaned_users @IsUseSamePwd =0 ,
2: @LoginName ='user1|user2|user3',
3: @Password ='Jk(*45(|Jk(*45(|Jk(*45(';
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2013-12-10 09:07:53
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡