Add a User to an SQL Server

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Intelligent Solutions Inc.

Version Compatibility:

More information:

This function uses the ADO connection object to add a user to a SQL server and grant the user permissions to a database specified by one of the parameters. YOU MUST CHANGE THE FUNCTION TO SUPPLY YOUR OWN CONNECTION STRING, as explained by the code's comments.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Function AddUserTOSQLServer(ByVal UserName As String, _
   ByVal Password As String, ByVal Database As String) As Boolean
'*************************************************************
'This function adds a user to an SQL server.  YOU MUST CHANGE
'THE CODE TO SUPPLY YOUR OWN CONNECTION STRING, WHERE IDENTIFIED
'BELOW.  A reference to ADO 2.0 or above is required

'Specifically, it adds a User with the Specified Password to
'the Public Group and provides permissions to the database
'specified as the third parameter.

'The return value is true if successful, false otherwise
'***********************************************************
Dim strConnString As String
Dim oConn As New ADODB.Connection
   
On Error GoTo ErrorHandler

'THIS IS WHERE YOU MUST PUT YOUR OWN CONNECTION STRING IN
'MAKE SUBSTITUTIONS FOR User ID, Password, and Data Source

strConnString = "Provider=SQLOLEDB.1;"
strConnString = strConnString & "User ID=sa;password=mypassword;"
strConnString = strConnString & "Initial Catalog=" & _
      Database & ";"
strConnString = strConnString & "Data Source=MySQLServer;"
strConnString = strConnString & "Use Procedure for Prepare=1;"
strConnString = strConnString & "Auto Translate=True;"
strConnString = strConnString & "Packet Size=4096"

oConn.Open strConnString
'Add user to database
oConn.Execute "USE Master"
oConn.Execute "EXEC sp_addlogin " & UserName & "," & _
  Password & "," & Database
    
'Grant user access to Database in public group
oConn.Execute "USE " & Database
oConn.Execute "EXEC sp_adduser " & UserName
AddUserTOSQLServer = True

ErrorHandler:
    If oConn.State <> 0 Then
        oConn.Close
    End If

Set oConn = Nothing
    
End Function