The Scream 3 killer is Roman Bridger, the director of Stab 3, who turns out to be Sidney's half brother and who is finally shot dead by Dewey after a brutal fight with Sidney. He faked his own death before the killings began. His motive is rejection: Maureen turned him away, and he says that rejection consumed him. He also claims the killings were meant to frame Sidney, and she does not accept that excuse.
What happens to Sidney is the quiet part. After the fight she stabs Roman several times, Dewey shoots him when he gets back up, and in the epilogue she returns home and leaves her gates and door open for the first time in a long while. Dewey and Gale share a hopeful moment as the other survivors gather. The film closes the story of the original trilogy, and the ending was reshot in January 2000, after principal photography had already wrapped in September 1999.
In my opinion, the reveal is the most interesting thing Scream 3 does and also the reason it divides people. Tying Ghostface to Maureen's hidden Hollywood past gives the trilogy a circular finish, since the whole saga began with Sidney grieving her mother. That being said, it could be argued that a motive built on backstory the audience had never heard before is a hard sell, and the mimicked voices on the phone are a clever idea that never quite pays off the way the opening calls of the earlier films did.
Why did some viewers not like Scream 3? Perhaps it is the sum of the changes: a new screenwriter in Ehren Kruger, a new setting after Columbine, and an ending that was reshot late. None of that makes it a flop, as the box office shows, but it helps explain why fans keep arguing about where it lands. For the full verdict on that debate, see scream movies ranked, and for every mask in the series, see ghostface killers and ghostface.