Visual Basic > Database SQL Stuff

Adding a user to an SQL server database

Adding a user to an SQL server database

Below is a simple routine which can be used to add users to a database. Note, you will need to create the connection which the appropriate rights to allow creation of a new user (eg. SA).

Option Explicit

'Purpose     :  This function adds a new user to an SQL server database.
'Inputs      :  UserName                    The name of the user to add.
'               Password                    The users password.
'               oConn                       An open connection to the database.
'               [Database]                  If specified will add user to this database,
'                                           rather than using the default database from the connection.
'Outputs     :  Returns True if successful, false if otherwise.
'Notes       :  Adds a User with the Specified Password to the Public Group
'               of a database.
'               Requires a reference to ADO 2.1 or greater
'               You will require the relevant permisssions to add a user (i.e System Administrator)

Public Function SQLServerAddUser(UserName As String, Password As String, oConn As ADODB.Connection, Optional Database As String) As Boolean
   
    On Error GoTo ErrFailed

    'Add user to database
    oConn.Execute "USE Master"
    If Len(Database) Then
        'Use the same database as the connection
        oConn.Execute "EXEC sp_addlogin " & UserName & "," & Password & "," & Database
        oConn.Execute "USE " & Database
    Else
        'Use a different database to the connection
        oConn.Execute "EXEC sp_addlogin " & UserName & "," & Password & "," & oConn.DefaultDatabase
        oConn.Execute "USE " & oConn.DefaultDatabase
    End If
        
    'Grant user access to Database in public group
    oConn.Execute "EXEC sp_adduser " & UserName
    SQLServerAddUser = True
    
    Exit Function
    
ErrFailed:
    Debug.Print "Error in SQLServerAddUser: " & Err.Description
    SQLServerAddUser = False
End Function


'Demonstration routine
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