>However whilst I know I could write a stored proc to do the writing to table etc I'm not really sure how to explicitly do a RLOCK in SQL Server. From what I've read SQL Server seems to perform a dynamic lock i.e. it determines itself whether the table should be page locked etc.
You are correct. SQL Server's lock manager does not support pessimistic locking. You could simulate it by starting a transaction and aquiring a lock on the required resource(s). The lock will be maintained until the transaction is terminated. But beware that this could have a serious impact to your concurrency.
What about using a timestamp on every row? When the user wants to edit a row, grab the timestamp and when the save is made, check that the timestamp hasn't been incremented.
-Mike