After restoring, incorrect database credentials are used
Some possible solutions / debugging steps:

Check that your local settings.php file is correct by using drush status command. 
 Do this when in the same directory as the settings.php file (or with a correctly set up site alias).  You should see information about the database and drupal version.  If you don't then something is wrong with your settings.

Next, Drupal uses the DB to cache things in the DB.  This cached information in an SQL dump can cause a local site to crash.  Some ways to possible ways to correct this are:


On the local site, try using the drush cc (assumed D7 per tag) on the command line. 


(OR)


Prior to making the dump from production, clear cache (either via admin screen or drush cc).


(OR)


Remove the contents of any Cache_* table on the local site (keep the tables)


Another possibility is that the password encryption salt string is different between the sites. This is set in the settings.php file and should be the same on both sites.

Finally, you can always reset the local user password via drush.  Two useful drush commands are:


drush uli --name=xxx  (Gets a password reset URL.. may need to put the correct host name)
drush upwd [username] [new-password]