I’ve seen this come up several times recently in the SQL Server Community. There are diagrams built in to SSMS, but they’re a bit limited in their usefulness. You can’t easily break them down into sub-models or tweak them without affecting the underlying database. There are 3rd party diagramming options for SQL Server, some free and some paid.
Simple-Talk ran an article calling for people to submit their favorite ERD software.
Database Answers has a page dedicated to modelling tools. I’m not sure when the page was last updated, but as far as I know the author is still maintaining the page as it points to some more recent offerings.
Database Answers also pointed to a Wikipedia page on Data Modelling Tools.
I’ve used ER/Studio for years and generally prefer it to ErWin, though that is probably moot at this point seeing that Embarcadero bought ErWin. It’s a pretty expensive piece of software, though. Not everyone can afford the licenses needed to run it. I hope the pointers to some other lists will help people find the right tool for their needs.