Here is another way without a subquery:

;with cte as (select *, count(*) over (partition by LastName, FirstName) as cntDups
from Users)

select * from cte where cntDups > 1

>I have a list of people that I need to do a duplicate name check on and display the results along with the IDs. I was going to do a simple GROUP BY, but then I'd have to join back to the original table to get the ID. Is there something sexier? (11:30 on a Saturday night and I'm asking about sexy SQL queries....)
>
>

>SELECT
>	*
>FROM
>	Users users
>		INNER JOIN (
>					SELECT
>						FirstName, LastName
>					FROM
>						Users
>					GROUP BY
>						FirstName, LastName
>					HAVING
>						COUNT(*) > 1
>				   ) un ON (un.FirstName = users.FirstName AND un.LastName = users.LastName)
>