Error 1044 Access denied for user to database: when your file tries to create the database

The 1044 nearly always turns up when you import an .sql file. It says that the user, already accepted, has no permission on that database. It looks like this: #1044 - Access denied for user 'abc_user'@'localhost' to database 'other_db'. Look at the name at the end: if it is not the database you are importing into, you have found the culprit.

On a shared hosting account, databases are not created with SQL statements: they are created in cPanel, under MySQL Databases. A file that comes from another server very often carries lines that try to do exactly that, and the database refuses.

What is usually in the file

The line in the file Why it fails here
CREATE DATABASE ... The account’s user cannot create databases through SQL. The database already exists: you made it in cPanel.
USE `other_db`; It orders work in a database with the source’s name, which does not exist here or is not yours. The commonest case of the 1044.
DROP DATABASE ... Dropping a whole database is not allowed through SQL either. Empty it from phpMyAdmin if that is what you want.
DEFINER=... on views, triggers or routines It points to a user who is not you. It usually gives a different number (1227), but the cure is the same: remove the clause or change it to your user.
GRANT ... or CREATE USER ... Users and privileges are created in cPanel, not through SQL.

Fixing it, step by step

1 Create the database and the user in cPanel, and join them under Add User To Database with all privileges. See creating a database user and giving it the right privileges.
2 Copy the file and open the copy in a proper text editor. Big files need an editor built for it; Notepad can choke.
3 Look for the troublesome lines: CREATE DATABASE, USE, DROP DATABASE. With SSH, grep -n "CREATE DATABASE" file.sql tells you which line they are on.
4 Delete those lines (or put -- in front of them, which turns them into comments) and save.
5 In phpMyAdmin choose the right database in the left column before importing. That is the one that receives the tables. The file size limit has its own article: importing with phpMyAdmin.
A different prefix is not a file error. If the tables carry another installation’s prefix (say wp_ at the source and something else in the wp-config.php here), the site sees an empty database. Do not confuse it with permissions: check the application’s prefix.
File too big to open? Split the job: import over SSH, where the file does not pass through the browser and the bad lines can be removed with one command. See importing a large database over SSH.

Cleaned the file and the 1044 is still there? Tell us the name of the target database and the whole message.

Open a support ticket

SEE ALSO

Importing with phpMyAdmin, and the size limit that stops you

Importing a large database over SSH

Creating a database user and giving it the right privileges

Error 1045: the causes, in order

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from $6.60/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
Error 1064 You have an error in your SQL syntax: how to find the line The 1064 is the database saying it did not understand the SQL statement: it is written in a way the database does not ac...