SQL Server Grant Script doesn't work (4305759)

Running the SQL Server Grant Script manually or automatically through the wizard is not working.

Privileges are not sufficient for user foglightmon

Connection validation from Connection Details section shows Insufficient Privileges even after running the grant process with a sysadmin account.

Insufficient database privileges

Checking the properties of the Login, the User Mapping for the login shows some databases are not mapped. Screenshot shows this for user foglightmon.

Applying the User Mapping to the missing databases results in Error:15023 like this:

This is caused by orphaned Users in SQL Server system databases. Probably because this login was deleted and recreated in the past. When a SQL Server Login is deleted, the Users associated to the individual Databases remain behind as orphaned users.

Resolution 1.

Remove the existing user from each individual system database that is missing from the User Mapping for the affected login and then run the Grant Script manually.

Resolution 2.

Run sp_change_users_login with the 'Auto_Fix' parameter and the affected user name for each affected Database (e.g. master, tempdb, etc) and then run the Grant Script manually.