VB+SQLDMO实现SQLServer身份验证管理子程序

End Function

Sub 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
   
    '' 设置参数
    srvname = "(local)"
    suid = "sa"
    pwd = ""
    dbname = "NorthwindCS"
   
    '' 新建服务器实例
    Set srv1 = New SQLDMO.SQLServer
   
    '' 以 SQL Server 登录方式进行连接
    srv1.Connect srvname, suid, pwd
   
    '' 新建一个 SQL Server 登录对象 UserX
    suid = "UserX"
    Set lgn1 = New SQLDMO.Login
    lgn1.Name = suid
    lgn1.Database = dbname
    lgn1.SetPassword "", pwd
    srv1.Logins.Add lgn1
   
    '' 在数据库 NorthwindCS 上新建一个用户对象 UserX，并与登录对象 UserX 相关联
    Set usr1 = New SQLDMO.User
    usr1.Name = suid
    usr1.Login = lgn1.Name
    srv1.Databases(dbname).Users.Add usr1
    srv1.Databases(dbname).DatabaseRoles("db_datareader").AddMember usr1.Name
   
    '' 设置调用 ADP 的子程序参数
    prpath = "C:\Documents and Settings\Administrator\My Documents\"
    prname = "NorthwindCS"
   
    '' 该参数用于控制当前用户使用 Windows 登录
    '' 代替 SQL Server 的 suid 和 pwd
    bolWindowsLogin = False
   
    '' 使用 Windows 或 SQL Server 登录调用打开名为 prname ADP 的子程序
    OpenADPWindowsOrSQLServer srvname, dbname, prpath, prname, suid, pwd, bolWindowsLogin
End Sub

Sub CleanUpAfterMakeLoginWithDAtaReader()
    Dim srv1 As New SQLDMO.SQLServer
   
    srv1.Connect "(local)", "sa", ""
   
    srv1.Databases("NorthwindCS").Users.Remove "UserX"
    srv1.Logins.Remove "UserX"
End Sub