I am used to using SQL Management Studio Express on Windows and was wondering if there were any good ones with similar functionality and price (free) for Mac OS X.
Aqua Data Studio might suite you well.
Edit: Not free, my bad.
Take 2: I haven't used any, but according to Wikipedia databse tools comparison article there are some Java based magaers for OS X that are free.
Sequel Pro is the best SQL management tool I have used on OSX. I am not familiar with SQL Management Studio Express, but Sequel Pro is free. However, I think Sequel Pro only works with MySQL and if you are looking for another DBMS maybe take a look at Navicat.
I've been looking myself, and I think you will struggle to find anything free. Understandably there isn't much support for SQL Server on Mac, and most open source developers tend to use (and develop for) open source databases.
I have found RazorSQL which is very good, has a free trial and is pretty cheap. I may go as far as to buy it once my trial runs out. (It also supports lots of other databases).
Oracle SQL developer has support for most sql based databases. but the setup is a bit fiddly. it has both windows and mac and linux version. think its cross platform and free as well. currently tested mysql, oracle and mssql on it. mssql has some connection problems but i think its my server.
Unfortunately I can't vote up mg's answer because I don't have enough reputation yet, but Oracle SQL Developer is excellent and free. When you install it for the first time it will only support Oracle connections, but if you go Help>Check for Updates then it will let you select third party drivers for SQL Server, MySQL etc.