Creating a new user

The sp_adduser stored procedure shown below can be run by the database owner or anyone else who has been given an alias as the database owner. The syntax allows you to specify a user name other than the login name, but I can’t find anything to recommend your doing so. You also get to specify a group to which this user will belong. If you don’t specify a group, the user will belong only to public. Remember, all users are members of public, although each user can also be a member of one other group.

sp_adduser login_id [, username [, groupname]]

This stored procedure doesn’t return any results, so you will execute it differently, as shown here:

Private Sub AddUser(ByVal strLogin As String, _
    ByVal strGroup As String)
    Static qryAddUser As rdoQuery

    If qryAddUser Is Nothing Then
        Set qryAddUser = conPiConnection.CreateQuery("AddUser", _
            "sp_adduser ?, ?, ?")
    End If

    qryAddUser.rdoParameters(0) = strLogin ' Login ID
    qryAddUser.rdoParameters(1) = strLogin ' User name
    qryAddUser.rdoParameters(2) = strGroup ' Groupname

    qryAddUser.Execute
End Sub

Here we are creating a parameter query, setting the parameters, and executing the query. Parameters can be input or output parameters (or both). But the default direction is input, so we don’t have to explicitly specify the direction. The next time we need to execute the query, we will already have it and will only have to set the parameters and execute it. This shortens the execution time considerably.