SharePoint 2010: User Profile Synchronization Service fails to start with a message "Failed to configure ILM" in ULS

What?

I have setup a new SharePoint 2010 non-production farm in a single server environment with Active Directory, SQL Server 2008 and SharePoint 2010 installed. (Not stand-alone installation)
The dreaded User Profile Synchronization Service fails to start as usual in a newly setup SharePoint 2010 farm.

The following error was found in the ULS logs:

UserProfileApplication.SynchronizeMIIS: Failed to configure ILM, will attempt during next rerun. Exception: System.Data.SqlClient.SqlException: Specified collection 'StringSchemaCollection' cannot be dropped because it is used by object...

Note that the above message shows up as an entry with High priority and not as an exception in ULS log.

Why?

There could be hell a lot of reasons why the User Profile Synchronization Service fails to start. First of all, it does not work in a single server stand alone installation with inbuilt database.

In my case, there were a couple of issues and I will try to explain how I fixed them in this post.

Access denied for all users except Farm Account after a SharePoint 2010 content database restore

What?

I successfully did a restore of a SharePoint 2010 site from production into a dev farm which is in a different domain. I am able to access the site normally using the farm account. All other accounts got access denied including the Setup Account.

I verified that the correct application pool is being used, tried to grant access to the setup account as well as the search account at web application level as well but no use.

Why?

The super reader and writer accounts are required to be configured in order for permissions to work normally.