I have recently moved several websites from own hosting to Microsoft Azure.

If I try to add a new extension using the extension wizard I receive the following error message.

A critical error has occurred. Could not find stored procedure 'dbo.GetPackageType'.

Can you advise the best way to get the stored procedures working again properly as there seems to be either a permissions error or something similar.

It would be helpful if we could achieve this without rebuilding the website(s) completely.

Most likely whatever account you have DNN accessing SQL with does not have Execute permission on the stored procedures in the database.  The account you're using should be a member of the dbowner role, because DNN has to change the schema for updates and so forth.

Check the connection string in web.config.  Find the account you're using.  Make sure it has dbowner on the site database.  Also, bear in mind that if you are using a mixed mode account (i.e. named and not a Windows account), you will actually need to remove the account from the database and re-add it after a restore.  A named account in a database restored to a different server won't resolve correctly.  So, like this:

use [database]

DROP USER sqllogin

CREATE USER sqllogin FOR LOGIN sqllogin

exec sp_addrolemember 'db_owner', 'sqllogin'