Sunday, October 28, 2012

How to Resolve SQL Server Error Message 18456?

When you try to connect with SQL server database, you could get success in most of the cases but sometimes you may fail to connect with SQL server database too. In case of failure you may get “Error: 18456, Severity: 14, State: 38” error message in the error log. If you are running SQL Server 2008 R2 database on Window server 2008 R2 then I will assist you some tips to get rid from this problem.

Reason: SQL server 2008 R2 database generates error message 18456 when It finds the desired database is either offline or not accessible. You can't access a database when it is in restoring state, single user mode, autoclose etc.

Solution: Solution for these types of problem depends upon the nature of the database like a new database or an old database.

Condition 1: If you have recently created your database under different user account then make sure you are trying to connect with same user account. Sometimes we forget to give full permission to newly created database, so check the permission for newly created database. 
Condition 2: If you are trying to connect with an old database then check the status of that database in sys.databases and the query for check is:

SELECT state_desc, user_access_desc, is_auto_close_on, is_in_standby FROM sys.databases WHERE name = 'your database name';

Note: If you don't want to specify the name of database then check default database for login and the query is:

SELECT default_database_name FROM sys.server_principals WHERE name = N'login_name';

No comments:

Post a Comment