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