Sir is always followed by the forename or the full name, but not the surname.

With the correction to Dr. as others pointed out it should be Sir Brian, Sir Brian May, or Professor Sir Brian May. Additional the PhD suffix could be added.

Edit: he doesn’t currently have a professor position so that would likely be incorrect here. He was previously a university Chancellor so that may be more correct to use, though I am uncertain how the different terms would be used in this case.