P°idßnφ u₧ivatele do databßze SQL Serveru

Postup:
Aby nßsledujφcφ procedura fungovala, musφte mφt vytvo°enΘ p°ipojenφ na SQL databßzi s prßvy pro p°idßvßnφ u₧ivatel∙ - nejlΘpe jako SA. Tento postup navφc vy₧aduje mφt v projektu referenci na ADO 2.1.

Option Explicit

Public Function SQLServerAddUser(UserName As String, _
   Password As String, oConn As ADODB.Connection, _
   Optional Database As String) As Boolean

'Vstup : UserName - jmΘno u₧ivatele, kterΘ chcete p°idat
'              Password - heslo novΘho u₧ivatele
'              oConn - otev°enΘ p°ipojenφ na databßzi
'            [Database] - pokud je specifikovßno, p°idß u₧ivatele do tΘto databßze
'                               mφsto pou₧itφ defaultnφ databßze z p°ipojenφ
'V²stup : Vracφ True p°i ·sp∞sÜnΘm provedenφ, jinak False

   On Error GoTo ErrFailed

   oConn.Execute "USE Master"
   If Len(Database) Then
       oConn.Execute "EXEC sp_addlogin " & UserName & "," & _
           Password & "," & Database
       oConn.Execute "USE " & Database
   Else
      oConn.Execute "EXEC sp_addlogin " & UserName & "," & _
         Password & "," & oConn.DefaultDatabase
      oConn.Execute "USE " & oConn.DefaultDatabase
   End If

   oConn.Execute "EXEC sp_adduser " & UserName
   SQLServerAddUser = True

Exit Function

ErrFailed:
   Debug.Print "SQLServerAddUser: " & Err.Description
   SQLServerAddUser = False

End Function


P°φklad pou₧itφ:
Sub Test()

   Dim sConString As String
   Dim oConn As ADODB.Connection

   sConString = "Provider=SQLOLEDB.1;"
   sConString = sConString & "User ID=sa;password=mypassword;"
   sConString = sConString & "Initial Catalog=MyDatabase;"
   sConString = sConString & "Data Source=MySQLServer;"

   oConn.Open sConString
   SQLServerAddUser "TestUser", "TestPassword", oConn

End Sub

Zp∞t