It prevents the user from logging in until they have responded to an activation email that is sent by Discourse. If the user attempts to login, they will see a notice similar to this:
Note that deactivating a user does not trigger the activation email to be sent to them. You can trigger an activation email to be sent to a user by clicking the “Send Activation Email” button that is displayed in the Activated section of their admin page. The user can also trigger the activation email to be sent by clicking the “Resend Activation Email” button from the notice that is displayed to them when they attempt to login to the site.