How to add user, change password in SQLServer from MSAccess. 1

[Goondu]

I know how to connect to SQL Server. But how do you add user or change the user password in the SQL Server from Microsoft Access?

Does anyone have a link or sample codes?

 

[jrbarnett]

Use your ADO connection to run the SQL Server system stored procedures with appropriate parameters.

The exact code you will need depends on the version of SQL Server that you use.

John

 

[Goondu]

Thanks John,

Store Procedures....so in SQL Server. Will it work in SQL Server Express?

Do you have any good links on this, so that I can learn more and where can I learn on how to create SP?

 

[jrbarnett]

You don't need to create the stored procedures, they are supplied with SQL Server, even the express edition.

Have a look in Forum183 for more information on transact SQL programming (the langauge used for writing sQL Server stored procedures).

John

 

[Goondu]

John,

Thanks, there's no SP in my SQL Express 2005 but I can create them if needed. I wish that was a guide regarding this, can't google it to find them.

 

[jrbarnett]

Note this is adapted from my code, I haven't fully removed the external dependencies on this. It expects a global command object (gcnn) open to the server with valid credentials.

It validates the current id/password before allowing a change, and deliberately it doesn't allow changes to the sa password.
Hope this will give you something to work with.

Note - you will need a reference to the ADO object library set in your access database.

John

Private Function UpdatePassword() As Boolean
' ******************************************************************************
'
' Function to execute the sp_password stored procedure on the SQL server
' used to allow users to change their own password
' Written by J Barnett (MIS Officer), 12-15 May 2006.
'
*******************************************************************************
    
    Dim lngCount As Long
    Dim cmd As ADODB.Command
    
    On Error GoTo err_DBConnect
    
    ' Build SQL Server TCP/IP connection string based on the username and existing password on the form.
    ' Components are as follows:
    ' Provider=SQLOLEDB - Driver
    ' Data Source = IP address, port of server
    ' Network Library = Database connection library to use (this means "use TCP/IP")
    ' Initial catalog = Database to switch to
    ' User ID and password are the details of the SQL accounts to connect as
    strDBConnect = "PROVIDER='SQLOLEDB';Data Source=<server dns name or ip address>;Network Library=DBMSSOCN;Initial Catalog=<dbname>;User id = " & Me.txtUsername & "; Password = " & Me.txtCurrentPass & ";"
    
    If Me.txtUsername = "sa" Then
        ' Don't let anyone change the SQL Server SA account via this application, just let it fail gracefully...
        UpdatePassword = False
        Exit Function
    End If

    ' Connect to the server

    ' Set up a command object to execute the stored procedure
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = gcnn
        .CommandType = adCmdStoredProc ' Evaluate CommandText as a stored procedure (rather than plain text or autodetect, runs faster).
        .CommandText = "sp_password"    ' Name of SP to execute
        .CommandTimeout = 30            ' Timeout to fail after (seconds)
        
        .Parameters.Append .CreateParameter("old", adVarChar, adParamInput, 128, Me.txtCurrentPass) ' old parameter = current password
        .Parameters.Append .CreateParameter("new", adVarChar, adParamInput, 128, Me.txtNewpass) ' new parameter = new password
        .Execute lngCount ' execute SP and return the number of rows affected to lngCount variable

    End With

Exit_DBConnect:
    If lngCount = 0 Then
        UpdatePassword = False
    Else
        UpdatePassword = True
    End If
    Exit Function
    
err_DBConnect:
    Select Case Err.Number
        Case -2147217843 ' Incorrect username/password
            UpdatePassword = False
            Resume Exit_DBConnect
            
        Case Else
            If Err.Number <> 0 Then Debug.Print Err.Number & " " & Err.Description
            Resume Next
    End Select
End Function

 

[Goondu]

Thanks John, for the VB sample with the parameters.

One last thing, is the sp_password procedure downloadable from the web or I need to create it? To create or change the password is the table name "sysusers" or there are more than one system table?

I'm new to SQL Server procedures.

 

[jrbarnett]

As I said before, it is supplied with SQL Server, even the express edition.

You can try it yourself by logging into the SQL Server using SQLCMD or management studio (freely downloadable for the express edition) and running it through that.
All my code does is call it.

John

 

[Goondu]

Ok Thanks John. Will search for the SP.