Solution for failing to open the user's default database

The default database cannot be opened and logon fails, which is also a problem familiar to SQL Server users. When using Enterprise Manager, query analyzer, various tools and application software, you may encounter this problem as long as it is related to connecting to the SQL Server database, there are many causes of this error. Next we will analyze in detail the causes and solutions.

I. Reasons

The default database of the Logon account is deleted.

Ii. solution:

(1) Use the Administrator account to modify the default database of this account

1. Open the Enterprise Manager, expand the server group, and then expand the server

2. Expand "security", expand logon, right-click the corresponding Logon account, and select Properties from the pop-up menu.

3. reselect the default database for this login account

(2) If no other administrator has logged on to the account and cannot be modified in the Enterprise Manager, use the isql command line tool.

Isql/U "sa"/P "sa password"/d "master"/Q "exec sp_defaultdb N 'sa', N 'master '"

If you use the Windows authentication method, use the following command line to change the default database to a non-lost database:

Isql/E/d "master"/Q "exec sp_defaultdb N 'builtinadministrators ', N 'master '"

(2) cannot be started because the system password is changed

Solution:

Click run and type services. msc, open the service program, find the SQLSERVER project, select attribute Item 2-'login', change the logon password to the logon password of the Operating System Administrator account, and restart SQLSErver.

SQL Server 2005

In SQL Server 2005, you can use the sqlcmd utility to change the default database. To do this, follow these steps: ◆ 1. Click Start, click Run, type cmd, and then press Enter.

◆ 2. use one of the following methods based on the type of authentication used for SQL Server login: • If SQL Server login uses Microsoft Windows authentication to connect to this instance, type the following at a command prompt, then press Enter:

Sqlcmd-E-S InstanceName-d master

• If you log on to SQL Server and use SQL Server authentication to connect to this instance, type the following in a command prompt and press Enter:

Sqlcmd-S InstanceName-d master-U SQLLogin-P Password

Note: InstanceName is a placeholder for the name of the SQL Server 2005 instance to be connected. SQLLogin is a placeholder for deleting the SQL Server Logon of its default database. Password is a placeholder for the SQL Server login Password.

◆ 3. At the sqlcmd prompt, type the following content and press Enter:

Alter LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName

Note: AvailDBName is a placeholder for the name of an existing database that can be accessed by the SQL Server in the instance.

◆ 4. At the sqlcmd prompt, type GO and press Enter.