SQLServer身份驗(yàn)證管理子程序

1、Option Compare Database Public appAccess As Access.Application Sub CallSQLDMOSQLServerLogin() Dim srvname As String Dim suid As String Dim pwd As String ' 設(shè)置 SQL Server 的登錄參數(shù) srvname = "(local)" 'suid = "sa" 'pwd = "" ' 調(diào)用 SQL Server 登錄過(guò)程 SQLDMOSQLServer

2、Login srvname, suid, pwd End Sub Sub SQLDMOSQLServerLogin(srvname As String, suid As String, pwd As String) Dim srv1 As SQLDMO.SQLServer ' 新建一個(gè)服務(wù)器實(shí)例 Set srv1 = New SQLDMO.SQLServer ' 調(diào)用 SQL Server 登錄連接方法 srv1.Connect srvname, suid, pwd ' 斷開(kāi)連接 srv1.Disconnect Set srv1 = Nothing End Sub Su

3、b CallSQLDMOWindowsLogin() Dim srvname As String ' 設(shè)置 Windows 登錄參數(shù) srvname = "(local)" SQLDMOWindowsLogin srvname End Sub Sub SQLDMOWindowsLogin(srvname As String) Dim srv1 As SQLDMO.SQLServer ' 新建一個(gè)服務(wù)器實(shí)例 Set srv1 = New SQLDMO.SQLServer ' 在調(diào)用前，設(shè)置 LoginSecure 屬性為 True ' 使用服務(wù)

4、名進(jìn)行連接 srv1.LoginSecure = True srv1.Connect srvname ' 斷開(kāi)連接 srv1.Disconnect Set srv1 = Nothing End Sub Sub CallChangeServerAuthenticationMode() Dim constAuth As Byte ' 設(shè)置 constAuth 參數(shù)為： ' SQLDMOSecurity_Integrated 為 Windows Authentication 模式 ' SQLDMOSecurity_Mixed 為 Mixed Authenticatio

5、n 模式 ' 設(shè)置 constAuth 的默認(rèn)值 constAuth = SQLDMOSecurity_Mixed ' 調(diào)用改變 SQL Server 身份認(rèn)證模式的方法 ChangeServerAuthenticationMode constAuth End SubSub ChangeServerAuthenticationMode(constAuth As Byte) Dim srv1 As SQLDMO.SQLServer ' 指定哪個(gè)服務(wù)器，默認(rèn)為 Local (本地服務(wù)器) srvname = "(local)" ' 使用集成安全(

6、Windows)方式新建一個(gè) SQL Server 對(duì)象并進(jìn)行連接 Set srv1 = New SQLDMO.SQLServer srv1.LoginSecure = True srv1.Connect srvname ' 設(shè)置 SecurityMode 屬性為 Windows 或混合身份驗(yàn)證模式 srv1.IntegratedSecurity.SecurityMode = constAuth srv1.Disconnect ' 調(diào)用 Stop 方法停止服務(wù)器，直到服務(wù)器完全停止 srv1.Stop Do Until srv1.Status = SQLDMOSvc_Stopp

7、ed Loop ' 重新以混合模式啟動(dòng)服務(wù)器 srv1.Start True, srvname ' 斷開(kāi)連接 srv1.Disconnect Set srv1 = Nothing End Sub Sub ToWindowsAuthentication() Dim srv1 As SQLDMO.SQLServer ' 指定哪個(gè)服務(wù)器，默認(rèn)為 Local (本地服務(wù)器) srvname = "(local)" ' 使用集成安全(Windows)方式新建一個(gè) SQL Server 對(duì)象并進(jìn)行連接 Set srv1 = New SQLDMO.SQLS

8、erver srv1.LoginSecure = True srv1.Connect srvname ' 設(shè)置 SecurityMode 屬性為 Windows 身份驗(yàn)證模式 srv1.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Integrated srv1.Disconnect ' 調(diào)用 Stop 方法停止服務(wù)器，直到服務(wù)器完全停止 srv1.Stop Do Until srv1.Status = SQLDMOSvc_Stopped Loop ' 重新以混合模式啟動(dòng)服務(wù)器 srv1.Start True, srv

9、name ' 斷開(kāi)連接 srv1.Disconnect Set srv1 = Nothing End Sub Sub WindowsToMixedAuthentication() Dim srv1 As SQLDMO.SQLServer ' 指定哪個(gè)服務(wù)器，默認(rèn)為 Local (本地服務(wù)器) srvname = "(local)" ' 使用集成安全(Windows)方式新建一個(gè) SQL Server 對(duì)象并進(jìn)行連接 Set srv1 = New SQLDMO.SQLServer srv1.LoginSecure = True srv1.Connect

10、 srvname ' 設(shè)置 SecurityMode 屬性混合身份驗(yàn)證模式 srv1.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed srv1.Disconnect ' 調(diào)用 Stop 方法停止服務(wù)器，直到服務(wù)器完全停止 srv1.Stop Do Until srv1.Status = SQLDMOSvc_Stopped Loop ' 重新以混合模式啟動(dòng)服務(wù)器 srv1.Start True, srvname ' 斷開(kāi)連接 srv1.Disconnect Set srv1 = Nothing End

11、SubSub CallOpenADPWindowsOrSQLServer() Dim srvname As String Dim dbname As String Dim prpath As String Dim prname As String Dim suid As String Dim pwd As String Dim bolWindowsLogin As Boolean ' 設(shè)置打開(kāi) ADP 程序的參數(shù) srvname = "(local)" dbname = "NorthwindCS" ' ADP 連接的數(shù)據(jù)庫(kù) prpath

12、= "C:Documents and SettingsAdministratorMy Documents" ' ADP 文件所在的磁盤位置 prname = "NorthwindCS" ' ADP 文件名 suid = "msdn5" pwd = "password" ' 該參數(shù)用于控制當(dāng)前用戶使用 Windows 登錄 ' 代替 SQL Server 的 suid 和 pwd bolWindowsLogin = False ' 使用 Windows 或 SQL Server

13、 登錄調(diào)用打開(kāi)名為 prname ADP 的子程序 OpenADPWindowsOrSQLServer srvname, dbname, prpath, prname, suid, pwd, bolWindowsLogin End Sub Sub OpenADPWindowsOrSQLServer(srvname As String, dbname As String, _ prpath As String, prname As String, _ suid As String, pwd As String, bolWindowsLogin As Boolean) Dim bolLeaveOp

14、en As Boolean Dim strPrFilePath As String Dim sConnectionString As String ' 是否保持現(xiàn)有打開(kāi)的程序? If MsgBox("在該過(guò)程中是否關(guān)閉打開(kāi)的 ADP?", vbYesNo) = vbYes Then bolLeaveOpen = True End If ' 新建 Access 會(huì)話實(shí)例 (使用 .9 : Access 2000, .10 : Access 2002) Set appAccess = CreateObject("Access.Application.9

15、") ' 使用登錄名和口令打開(kāi) ADP 并使其可視 strPrFilePath = prpath amp; prname appAccess.OpenAccessProject strPrFilePath appAccess.Visible = True ' 指定 ADP 使用的新的 Windwos 或 SQL Server 登錄方式 If bolWindowsLogin Then appAccess.CurrentProject.OpenConnection _ "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;&

16、quot; amp; _ "PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" amp; _ dbname amp; "DATA SOURCE=" amp; srvname Else sConnectionString = "PROVIDER=SQLOLEDB.1;INITIAL CATALOG=" amp; _ dbname amp; "DATA SOURCE=" amp; srvname appAccess.CurrentProject.OpenConnection _

17、sConnectionString, _ suid, pwd End If ' 按上面提出的要求關(guān)閉 Access 會(huì)話實(shí)例 If bolLeaveOpen = False Then appAccess.CloseCurrentDatabase Set appAccess = Nothing End If End Sub Sub CallLoginDemo() Dim srvname As String Dim suid As String Dim pwd As String ' 設(shè)置登錄 SQL Server 的參數(shù) srvname = "(local)"

18、 suid = "sa" pwd = "" ' 調(diào)用 SQL Server 登錄子程序 LoginDemo srvname, suid, pwd End SubSub LoginDemo(srvname As String, suid As String, pwd As String) Dim srv1 As SQLDMO.SQLServer Dim lgn1 As SQLDMO.Login ' 新建一服務(wù)器實(shí)例 Set srv1 = New SQLDMO.SQLServer ' 以 SQL Server 方式連接 SQL Ser

19、ver srv1.Connect srvname, suid, pwd ' 新建登錄對(duì)象 Set lgn1 = New SQLDMO.Login ' 添加默認(rèn) SQL Server 登錄 msdn6 lgn1.Name = "UserX" lgn1.Database = "NorthwindCS" lgn1.SetPassword "", "password" srv1.Logins.Add lgn1 '重新新建登錄對(duì)象 Set lgn1 = New SQLDMO.Login ' 添

20、加基于 Windows 組 SQL_users 的登錄對(duì)象 lgn1.Name = "MYDESKUserSQL" lgn1.Database = "NorthwindCS" lgn1.Type = SQLDMOLogin_NTGroup srv1.Logins.Add lgn1 ' 在添加登錄后，列出登錄列表 Debug.Print "添加 2 個(gè)新登錄后的登錄列表:" For Each lgn1 In srv1.Logins Debug.Print DecodeLoginType(lgn1.Type), lgn1.Name

21、 Next lgn1 ' 刪除剛新建的登錄 srv1.Logins.Remove "MYDESKUserSQL" srv1.Logins.Remove "UserX" ' 列出登錄列表 Debug.Print vbCr amp; "刪除 2 個(gè)新登錄后的登錄列表:" For Each lgn1 In srv1.Logins Debug.Print DecodeLoginType(lgn1.Type), lgn1.Name Next lgn1 ' 斷開(kāi)連接 srv1.Disconnect Set srv1 = N

22、othing End Sub Function DecodeLoginType(lgn_type As Byte) As String Select Case lgn_type Case 0 DecodeLoginType = "SQLDMOLogin_NTUser" Case 1 DecodeLoginType = "SQLDMOLogin_NTGroup" Case 2 DecodeLoginType = "SQLDMOLogin_Standard" Case Else DecodeLoginType = "超出范圍&q

23、uot; End Select End FunctionSub MakeLoginWithDatareaderUser() Dim srv1 As SQLDMO.SQLServer Dim lgn1 As SQLDMO.Login Dim usr1 As SQLDMO.User Dim srvname As String Dim suid As String Dim pwd As String Dim dbname As String Dim prpath As String Dim prname As String Dim bolWindowsLogin As Boolean ' 設(shè)

24、置參數(shù) srvname = "(local)" suid = "sa" pwd = "" dbname = "NorthwindCS" ' 新建服務(wù)器實(shí)例 Set srv1 = New SQLDMO.SQLServer ' 以 SQL Server 登錄方式進(jìn)行連接 srv1.Connect srvname, suid, pwd ' 新建一個(gè) SQL Server 登錄對(duì)象 UserX suid = "UserX" Set lgn1 = New SQLDMO.Login lgn1.Name = suid lgn1.Database = dbname lgn1.SetPassword "", pwd srv1.Logins.Add lgn1 ' 在數(shù)據(jù)庫(kù) NorthwindCS 上新建一個(gè)用戶對(duì)象 UserX，并與登錄對(duì)象