熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

一組SQL Server身份驗證管理子程序

2013-11-15 14:37:24  來源: SQL Server 

  Option Compare Database
  Public appAccess As AccessApplication
  
  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 srv As SQLDMOSQLServer
    
     新建一個服務器實例
    Set srv = New SQLDMOSQLServer
    
     調用 SQL Server 登錄連接方法
    srvConnect srvname suid pwd
    
    
     斷開連接
    srvDisconnect
    Set srv = Nothing
  End Sub
  
  
  Sub CallSQLDMOWindowsLogin()
    Dim srvname As String
    
     設置 Windows 登錄參數
    srvname = (local)
    
    SQLDMOWindowsLogin srvname
  End Sub
  
  
  Sub SQLDMOWindowsLogin(srvname As String)
    Dim srv As SQLDMOSQLServer
    
     新建一個服務器實例
    Set srv = New SQLDMOSQLServer
    
     在調用前設置 LoginSecure 屬性為 True
     使用服務名進行連接
    srvLoginSecure = True
    srvConnect srvname
    
     斷開連接
    srvDisconnect
    Set srv = 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 srv As SQLDMOSQLServer
    
     指定哪個服務器默認為 Local (本地服務器)
    srvname = (local)
    
     使用集成安全(Windows)方式新建一個 SQL Server 對象並進行連接
    Set srv = New SQLDMOSQLServer
    srvLoginSecure = True
    srvConnect srvname
    
     設置 SecurityMode 屬性為 Windows 或混合身份驗證模式
    srvIntegratedSecuritySecurityMode = constAuth
    srvDisconnect
    
     調用 Stop 方法停止服務器直到服務器完全停止
    srvStop
    Do Until srvStatus = SQLDMOSvc_Stopped
    Loop
    
     重新以混合模式啟動服務器
    srvStart True srvname
    
     斷開連接
    srvDisconnect
    Set srv = Nothing
  End Sub
  
  Sub ToWindowsAuthentication()
    Dim srv As SQLDMOSQLServer
    
     指定哪個服務器默認為 Local (本地服務器)
    srvname = (local)
    
     使用集成安全(Windows)方式新建一個 SQL Server 對象並進行連接
    Set srv = New SQLDMOSQLServer
    srvLoginSecure = True
    srvConnect srvname
    
     設置 SecurityMode 屬性為 Windows 身份驗證模式
    srvIntegratedSecuritySecurityMode = SQLDMOSecurity_Integrated
    srvDisconnect
    
     調用 Stop 方法停止服務器直到服務器完全停止
    srvStop
    Do Until srvStatus = SQLDMOSvc_Stopped
    Loop
    
     重新以混合模式啟動服務器
    srvStart True srvname
    
     斷開連接
    srvDisconnect
    Set srv = Nothing
  
  End Sub
  
  Sub WindowsToMixedAuthentication()
  Dim srv As SQLDMOSQLServer
  
     指定哪個服務器默認為 Local (本地服務器)
    srvname = (local)
    
     使用集成安全(Windows)方式新建一個 SQL Server 對象並進行連接
    Set srv = New SQLDMOSQLServer
    srvLoginSecure = True
    srvConnect srvname
    
     設置 SecurityMode 屬性混合身份驗證模式
    srvIntegratedSecuritySecurityMode = SQLDMOSecurity_Mixed
    srvDisconnect
    
     調用 Stop 方法停止服務器直到服務器完全停止
    srvStop
    Do Until srvStatus = SQLDMOSvc_Stopped
    Loop
    
     重新以混合模式啟動服務器
    srvStart True srvname
    
     斷開連接
    srvDisconnect
    Set srv = 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 Settings\Administrator\My Documents\   ADP 文件所在的磁盤位置
    prname = NorthwindCS  ADP 文件名
    suid = msdn
    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 會話實例 (使用 : Access : Access )
    Set appAccess = CreateObject(AccessApplication)
    
     使用登錄名和口令打開 ADP 並使其可視
    strPrFilePath = prpath & prname
    appAccessOpenAccessProject strPrFilePath
    appAccessVisible = True
    
     指定 ADP 使用的新的 Windwos 或 SQL Server 登錄方式
    If bolWindowsLogin Then
      appAccessCurrentProjectOpenConnection _
        PROVIDER=SQLOLEDB;INTEGRATED SECURITY=SSPI; & _
        PERSIST SECURITY INFO=FALSE;INITIAL CATALOG= & _
        dbname & ;DATA SOURCE= & srvname
    Else
      sConnectionString = PROVIDER=SQLOLEDB;INITIAL CATALOG= & _
        dbname & ;DATA SOURCE= & srvname
      appAccessCurrentProjectOpenConnection _
        sConnectionString _
        suid pwd
    End If
    
     按上面提出的要求關閉 Access 會話實例
    If bolLeaveOpen = False Then
      appAccessCloseCurrentDatabase
      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 srv As SQLDMOSQLServer
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22075.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.