Tag: SQL Security Issue

  • Can not open user default database; login failed

    Troubleshooting Authentication Issues

    Problem:

    Can not open user default database; login failed

    Scenario:

    I have windows DBA group which has default database DB1 and has SYSADMIN rights on SQL Server, by some reasons the DB1 database is corrupted and user is unable to login.

    Solution:

    Step 1:

    Connect SQL Server using SQLCMD/OSQL as below and give the default database name as MASTER

    sqlcmd -E -d master

    Step 2: Now issue ALTER LOGIN statement and change the default database

    Use SP_DefaultDB procedure to change the default database

    sp_defaultdb (SQL Server 2000)

    Changes the default database for a login

    Syntax

    EXEC sp_defaultdb 'Victoria', 'pubs'

    (SQL Server 2005)

    ALTER LOGIN LOGINNAME WITH DEFAULT_DATABASE = MASTER