Database access denied, properties missing

Question

We have a test system with SQL Server Express 2016 and SSMS.

We had a single database in this system, which was used for our product test.

This worked on Friday, and it does no longer work today.

We have restored the backup into a new database and the tests are working on that now, but we want to find out why the old one stopped working.

I have accessed the instance as the database server administrator, who had access to the database on Friday. I have then tried to access the database via SSMS. The error message is:

The database "producttest" is not accessible. (ObjectExplorer)

I have tried to open the database properties via SSMS. The error message was at first:

Property MaxDop is not available for Database 'producttest'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

and now the property has changed:

Property TargetRecoveryTime is not available for Database 'producttest'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

And I have opened a new query window and submitted the command USE producttest. The error message is:

Message 916, Level 14, State 1, Line 1
The server principal "DOMAIN\Administrator" is not able to access the database "producttest" under the current security context.

I have made a backup copy of the database files and tried to run DBCC CHECKDB producttest, but the message is

Message 102, Level 15, State 1, Line 1
Incorrect syntax near "producttest"

What could be the issue; what can I try now?

Answer 1

From the little data you shared, it may be a number of reasons.

Did you check the database still exists on the server? My first impression would be it has been deleted/dropped. SSMS, if it had been left open, doesn't refresh the view in Object Explorer.

If it is still there then it could have been taken offline. You can check DB status using methods listed here: https://www.mssqltips.com/sqlservertip/1477/methods-to-determine-the-status-of-a-sql-server-database/

and here: https://stackoverflow.com/questions/31866585/how-to-bring-back-offline-database-in-sql-server-2008

Additional items to check: are you logging to SQL server using SQL authentication? And are you logging as sa? If no and no, you might have to log in as sa and change ownership of the database to sa.

Also it might be a good idea to check if the file growth hasn't been halted due to file limits. But it might be impossible to do easy as you're unable to access Db properties. Use TSQL:

alter DATABASE [producttest]
modify file (name = 'producttest', maxsize = 128MB)
GO

After reading your comment I'm thinking access restrictions.