As part of the effort for developing a Windows Service, I restored a production database to a test database on the same SQL Server instance, and can access the test database just fine via SSMS. I gave db_owner role to the database to two other users that are unable to login, both getting SQL error

Error: 18456, Severity: 14, State: 38. Login valid but database unavailable (or login not permissioned)

Here is the basic message which mentions the database in question as the problem.

Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'MedFile_TestDataServer'. [CLIENT: ]

The database is not in "Restoring" status.

First user is NT Authority\System and the second a Windows user. Both credentials are used to run the Windows service that access the database in update mode, the system user from the same server, the Windows user from VS2013 running the service as a command program on my desktop. Both can get at other copies of this same database just by changing the database name so don't think a connection string issue. I have compared every property on the databases that work and do not work and see no differences except the file names and these two logins have less permissions on the databases that they can access.

Almost like this database is being kept unavailable after being restore but I can find no such property set on the database and I can access via SSMS. I've restarted the server containing the database ergo SQL Server as well.

I also tried running the service as a database administrator and get the same error even though that user accesses the db just fine via SSMS.

Is there anything that can make the database "unavailable"?

This is part of software development for a Windows service trying to use a test database. I am using both EF 6.02 and the latest ADO.NET version as well. I've checked out all the normal setup and it is correct.

Thanks for any ideas, Dave

  • You're not in single user mode, are you? – billinkc Jul 8 '14 at 15:36
  • Can you post a screenshot of the object explorer with the database list fully visible? – mrdenny Jul 8 '14 at 16:15
  • @billinkc - No not in single user mode. mrdenny - what specifically do you want to see? I don't want to show our whole database list. – Dave Jul 8 '14 at 16:36
  • Btw, I posted this to SO and am one vote away from being closed there since TPTB apparenlty decided the question was not appropriate there even though development related, so am posting here as suggested. – Dave Jul 8 '14 at 16:39
  • Any state information next to the database. Be sure to refresh the object explorer to get the current data as it doesn't update in real time. – mrdenny Jul 8 '14 at 18:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.