User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server [closed]

Posted on

Question :

I have a database on SQL Server. Yesterday It went to Suspect Mode, To recover that from the suspect mode I have followed some steps (Run some commands). But while running those commands it is giving me the error like : “User Does not have permission to alter database, the database does not exist, or the database is not in a state that allows access checks SQL server”. To resolve that issue I ran the SSMS with ‘run as administrator’ privilege, and I also tried to change the permissions for the database for the user, But it is not working. I am not able to delete that also and this is the second time I am facing this issue Now I have 2 databases in suspect mode

The image has the commands and the error result..

Answer :

It seems like there was a problem during the restoration process.

This error has nothing to do with permission. A database in suspect mode cannot be used or altered.

As mentionned, you will have to fix the issue with the database first (before you can run a “USE” or an “Alter database” on it).

If you do have backup for those database, restore the database from your backup in order to fix the issue.

If you don’t have backup, then it will be a bit harder and information about how your DBs went into suspect mode would be nice to have in order to see the best approch to bring them back online (potentially with data loss or corruption).

You may have to set the database into the Emergency state before running a checkdb with repair_allow_data_loss on the database (which does exactly as it said)

  1. Confirm that you are using a user that is member in the sysadmin

  2. Fix any issue in the database before executing this SP

  3. Try to restart the server with administration

Leave a Reply

Your email address will not be published. Required fields are marked *