sql db problem with windows authentication

Question

Have a SQL Server 2008 db which I connect to the Windows Authentication .. has worked good for 7-8 months .. but now when I come to work today it no longer worked to connect, without that I had done something Error message was:

Can not open user default database. Login failed. Login failed for user 'Jimmy-PC \ Jimmy'.

where the first is the computer name and the second is the user. The problem seems to be that it tries to connect to the default database. Have tried to change it without success .. I do not have sql server management tools for sql 2008 but only to 2005, someone who has similar experience? who have not touched anything said over the weekend and it worked last Friday without any problems.

Answer 1

It is strange that it just stopped working. Have you checked the status of the user's default database? Make sure the database exists and is online.

To find the user's default database run the following command using sqlcmd.exe

select dbname from syslogins where name = 'Jimmy-PC \ Jimmy'

I have seen this error when recovering a system and the user's default database hadn't been restored yet and another time when a database was in "Recovery" mode.

I would also make sure that the windows account is not disabled.

If you find that you need to change the user's default database you can use a T-SQL command, see the following question:

http://stackoverflow.com/questions/105950/how-do-i-set-the-default-database-in-sql-server-from-code

Answer 2

The user's default database is offline. the thing that changed since yesterday is that your database had failed, for whatever reason.

You need to log in with a different account, one that does not have a default database, and inspect the state of the database. If you do not have such an account, then you can use an administrator account. If the administrators group is not granted login into SQL Server then see this article Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out.

Once you can log in, the only tool you need is sqlcmd.exe, which is installed on your machine. Look into sys.databases, the state_desc column will tell if a database is offline. You can try a to ALTER DATABASE ... SET ONLINE and see if it recovers, but that would be unlikely. Your best choice will be to apply your disaster recovery plan and restore the database from the backups you keep for it.