If possible, I would recommend using the dedicated database model. Therefore, each developer would have their own copy of the database and their changes will NOT impact anyone else until the other users explicitly decide to Get Latest. Other users can see exactly what changes have been made before they update their copy.
With or without SQL Source Control, you will always have this problem of overwritting another developer's changes if multiple developers are working against the same db.
In a dedicated model, if 2 developers work on the same object, the first one to commit will have no problems. The second developer will see a conflict when they try to commit. They can decide to take the changes in source control and then they may need to reapply the changes that they made. Or, they can decide to keep their changes and reapply the other devs' changes. (For complex changes, I would recommend using a seperate merge tool to merge the 2 scripts.)
I'd love to hear more about your evaluation and anything that would prevent you from going to a dedicated model.
If you have to work on a shared model, then hopefully this will be rare when it could cause a problem like you say. All users should see blue indicators on the Object Explorer, which indicates changes which have not been committed yet. This is not the same as locking an object so other users can not edit it, but it could be a good warning that someone else currently has edits to that object, so I better wait till they're finished.
Currently, we have not implemented an exclusive checkout feature. You may want to vote comment on this feature request at https://redgate.uservoice.com/forums/39 ... ?ref=title
I just had a quick chat w/ David... Unfortunately, linking to a working folder independent of TFS still won't help stop multiple users in the same database from overwriting each others' changes.