Another vote for Embarcadero ER Studio. It's a very solid tool.
You might also want to look at SQL Dependency Tracker from Red Gate. It's not a modeling tool, but it's a great way to understand how a database is put together, how the stored procs and views are referencing each other, etc.
DISCLAIMER: I work for Red Gate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning