Not with the out-of-the-box components, at least for the in-premises installations. Activating a user account it means in most cases creating a user in Active Directory or at least if an alternate user store is configured you need to create/enable a form for registration, automate expiration, notifications, and so on.
Of course with custom solutions it could be achieved. Example (if you use AD):
- Build an account registration/activation page (this could be an
Application Page inheriting from UnsecurePageLayout, similar to the
sign-in page already in SharePoint 2010).
- Store registration account data into a custom list with minimal data, have an Approval running to make sure you check data, plus either a Workflow or an Event-Handler that is exploiting Active Directory Services .NET classes to create User Account in the appropiate OU. (of course, this assumes you get the power/approval from your Sys Administration to do so)
- Once process of creation finished, give the appropiate user the permissions on required sites.
If you use alternate user Store, such as SQL ASP.NET Membership database, similar could be achieved by exploiting the MembershipProvider API, and ASP.NET Controls within an Application page.