Drupal 7 Add user delete tab in user profile
You need to use hook_menu() instead of hook_menu_alter() because you are adding a new menu item rather than altering an existing one.

function YOUR_MODULE_menu() {
  $items['user/%user/cancel'] = array(
    'title' =&gt; 'Cancel profile',
    'page callback' =&gt; 'drupal_get_form',
    'page arguments' =&gt; array('user_cancel_confirm_form', 1),
    'access callback' =&gt; 'user_cancel_access',
    'access arguments' =&gt; array(1),
    'type' =&gt; MENU_LOCAL_TASK,
    'weight' =&gt; 10,
    'file' =&gt; 'user.pages.inc',
    'file path' =&gt; drupal_get_path('module', 'user'),
  );

  return $items;
}


Don't forget to return $items at the end of the function, since it is not passed by reference.

Though I'm not sure if the page callback and arguments are right, I didn't check that, I've just copied from your code.