mysql孤立锁_SQL Server解决孤立用户浅析

孤立用户概念 所谓孤立用户即指在服务器实例上未定义或错误定义了其相应 SQL Server 登录名的数据库用户无法登录到实例。 这样的用户被称为此服务器实例上的数据库的“孤立用户”。 如果删除了对应的 SQL Server 登录名，则数据库用户可能会变为孤立用户。

孤立用户概念

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

检测孤立用户

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

Code Snippet

当然如果你不想用系统自带的存储过程sp_change_users_login，其实检测孤立账号也很简单，一个简单的SQL语句即可搞定：

Code Snippet

从上面可以看出，

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 ;

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:

( 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:

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:

[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:

52: BEGIN

53:

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

54:

RETURN 1;

55: END

56:

@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:

#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:

#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:

#OrphanedUser

98: (

99:

UserName sysname,

100:

UserId

INT

101: )

102:

103:

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

105:

106:

108:

SELECT UserName FROM #OrphanedUser;

109:

110:

111: OPEN Cur_OrphanedUsers;

112:

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:

#OrphanedUser;

147:

148: IF @IsUseSamePwd = 0

149: BEGIN

#TempLoginNams;

#TempPassword;

152: END

153:

154: GO

其中该存储过程调用了一个Function成为SplitString，该函数是我从网上搜索得来的，作者不详，本来想自己重写该函数，后来觉得没有必要重复造轮子。因为这个函数完全满足我的需求。

Code Snippet

这个存储过程在执行时，有一个既可以说是小bug，也可以说没有验证的错误，就是登录名的密码设置如果过于简单，则执行

EXEC sp_change_users_login 'Auto_Fix', @UserName, NULL,   @tmpPwd; 则会报如下错误

本文原创发布php中文网，转载请注明出处，感谢您的尊重！