程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 一組SQLServer身份驗證管理子程序

一組SQLServer身份驗證管理子程序

編輯:關於SqlServer
Option Compare Database
  Public appAccess As Access.Application
  
  Sub CallSQLDMOSQLServerLogin()
    Dim srvname As String
    Dim suid As String
    Dim pwd As String
    
    ' 設置 SQL Server 的登錄參數
    srvname = "(local)"
    'suid = "sa"
    'pwd = ""
    
    ' 調用 SQL Server 登錄過程
    SQLDMOSQLServerLogin srvname, suid, pwd
  End Sub
  
  
  Sub SQLDMOSQLServerLogin(srvname As String, suid As String, pwd As String)
    Dim srv1 As SQLDMO.SQLServer
    
    ' 新建一個服務器實例
    Set srv1 = New SQLDMO.SQLServer
    
    ' 調用 SQL Server 登錄連接方法
    srv1.Connect srvname, suid, pwd
    
    
    ' 斷開連接
    srv1.Disconnect
    Set srv1 = Nothing
  End Sub
  
  
  Sub CallSQLDMOWindowsLogin()
    Dim srvname As String
    
    ' 設置 Windows 登錄參數
    srvname = "(local)"
    
    SQLDMOWindowsLogin srvname
  End Sub
  
  
  Sub SQLDMOWindowsLogin(srvname As String)
    Dim srv1 As SQLDMO.SQLServer
    
    ' 新建一個服務器實例
    Set srv1 = New SQLDMO.SQLServer
    
    ' 在調用前,設置 LoginSecure 屬性為 True
    ' 使用服務名進行連接
    srv1.LoginSecure = True
    srv1.Connect srvname
    
    ' 斷開連接
    srv1.Disconnect
    Set srv1 = Nothing
  End Sub
  
  Sub CallChangeServerAuthenticationMode()
    Dim constAuth As Byte
    
    ' 設置 constAuth 參數為:
    '  SQLDMOSecurity_Integrated 為 Windows Authentication 模式
    '  SQLDMOSecurity_Mixed 為 Mixed Authentication 模式
    
    ' 設置 constAuth 的默認值
    constAuth = SQLDMOSecurity_Mixed
    
    ' 調用改變 SQL Server 身份認證模式的方法
    ChangeServerAuthenticationMode constAuth
  End Sub
  
  Sub ChangeServerAuthenticationMode(constAuth As Byte)
    Dim srv1 As SQLDMO.SQLServer
    
    ' 指定哪個服務器,默認為 Local (本地服務器)
    srvname = "(local)"
    
    ' 使用集成安全(Windows)方式新建一個 SQL Server 對象並進行連接
    Set srv1 = New SQLDMO.SQLServer
    srv1.LoginSecure = True
    srv1.Connect srvname
    
    ' 設置 SecurityMode

屬性為 Windows 或混合身份驗證模式
    srv1.IntegratedSecurity.SecurityMode = constAuth
    srv1.Disconnect
    
    ' 調用 Stop 方法停止服務器,直到服務器完全停止
    srv1.Stop
    Do Until srv1.Status = SQLDMOSvc_Stopped
    Loop
    
    ' 重新以混合模式啟動服務器
    srv1.Start True, srvname
    
    ' 斷開連接
    srv1.Disconnect
    Set srv1 = Nothing
  End Sub
  
  Sub ToWindowsAuthentication()
    Dim srv1 As SQLDMO.SQLServer
    
    ' 指定哪個服務器,默認為 Local (本地服務器)
    srvname = "(local)"
    
    ' 使用集成安全(Windows)方式新建一個 SQL Server 對象並進行連接
    Set srv1 = New SQLDMO.SQLServer
    srv1.LoginSecure = True
    srv1.Connect srvname
    
    ' 設置 SecurityMode 屬性為 Windows 身份驗證模式
    srv1.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Integrated
    srv1.Disconnect
    
    ' 調用 Stop 方法停止服務器,直到服務器完全停止
    srv1.Stop
    Do Until srv1.Status = SQLDMOSvc_Stopped
    Loop
    
    ' 重新以混合模式啟動服務器
    srv1.Start True, srvname
    
    ' 斷開連接
    srv1.Disconnect
    Set srv1 = Nothing
  
  End Sub
  
  Sub WindowsToMixedAuthentication()
  Dim srv1 As SQLDMO.SQLServer
  
    ' 指定哪個服務器,默認為 Local (本地服務器)
    srvname = "(local)"
    
    ' 使用集成安全(Windows)方式新建一個 SQL Server 對象並進行連接
    Set srv1 = New SQLDMO.SQLServer
    srv1.LoginSecure = True
    srv1.Connect srvname
    
    ' 設置 SecurityMode 屬性混合身份驗證模式
    srv1.IntegratedSecurity.SecurityMode = SQLDMOSecurity_Mixed
    srv1.Disconnect
    
    ' 調用 Stop 方法停止服務器,直到服務器完全停止
    srv1.Stop
    Do Until srv1.St


您正在看的SQLserver教程是:一組SQLServer身份驗證管理子程序。atus = SQLDMOSvc_Stopped
    Loop
    
    ' 重新以混合模式啟動服務器
    srv1.Start True, srvname
    
    ' 斷開連接
    srv1.Disconnect
    Set srv1 = Nothing
  
  End Sub
  
  Sub 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
    
    ' 設置打開 ADP 程序的參數

/>    srvname = "(local)"
    dbname = "NorthwindCS" ' ADP 連接的數據庫
    prpath = "C:Documents and SettingsAdministratorMy Documents"  ' ADP 文件所在的磁盤位置
    prname = "NorthwindCS" ' ADP 文件名
    suid = "msdn5"
    pwd = "passWord"
    
    ' 該參數用於控制當前用戶使用 Windows 登錄
    ' 代替 SQL Server 的 suid 和 pwd
    bolWindowsLogin = False
    
    ' 使用 Windows 或 SQL Server 登錄調用打開名為 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 bolLeaveOpen As Boolean
    Dim strPrFilePath As String
    Dim sConnectionString As String
    
    ' 是否保持現有打開的程序?
    If MsgBox("在該過程中是否關閉打開的 ADP?", vbYesNo) = vbYes Then
      bolLeaveOpen = True
    End If
    
    ' 新建 Access 會話實例 (使用 .9 : Access 20

00, .10 : Access 2002)
    Set appAccess = CreateObject("Access.Application.9")
    
    ' 使用登錄名和口令打開 ADP 並使其可視
    strPrFilePath = prpath & prname
    appAccess.OpenAccessProject strPrFilePath
    appAccess.Visible = True
    
    ' 指定 ADP 使用的新的 Windwos 或 SQL Server 登錄方式
    If bolWindowsLogin Then
      appAccess.CurrentProject.OpenConnection _
        "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;" & _
        "PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & _
        dbname & ";DATA SOURCE=" & srvname
    Else
      sConnectionString = "PROVIDER=SQLOLEDB.1;INITIAL CATALOG=" & _
        dbname & ";DATA SOURCE=" & srvname
      appAccess.CurrentProject.OpenConnection _
        sConnectionString, _
        suid, pwd
    End If
    
    ' 按上面提出的要求關閉 Access 會話實例
    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
    
    ' 設置登錄 SQL Server 的參數
    srvname = "(local)"
    suid ="sa"
    pwd = ""
    
    ' 調用 SQL Server 登錄子程序
    LoginDemo srvname, suid, pwd
  End Sub
  
  
  Sub LoginDemo(srvname As String, suid As String, pwd As String)
    Dim srv1 As SQLDMO.SQLServer
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved