Visual Basic Programming Code Examples

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