Do I need to write level access on the dataset for the data to refresh on the server?

No this is not required.

Even if you have the read-level access on the database then also you will be able to publish the report without any issue. Just publish the data source to the server and schedule it to the Tableau server and it will keep on refreshing as per the schedule you have defined.