SQL Server Performance

Default database of sa user

Discussion in 'General DBA Questions' started by surendrakalekar, Nov 10, 2005.

  1. surendrakalekar New Member

    I am not able to edit/create SQL Server registration in EM by using sa user. It thows me the below error.
    -----------------------------------------
    SQL Server registration failed because of the connection failure displayed below. Do you wish to Modify anyway?
    Cannot open user default database. Login failed
    -----------------------------------------
    This problem is comming after removing the default database of sa user.
    With different user I tried with the command like ...
    EXEC sp_defaultdb 'sa', 'master'
    EXEC sp_grantdbaccess 'sa', 'master'
    but not able to run because those user's are not from admin group.

    How to fix this problem?



    Surendra Kalekar

  2. Chappy New Member

    restore or recreate that database using another user if possible
    then you can log in as sa and change your default database.
    Maybe theres an easier way, ive never had it happen on an sa account so I dont really know

    Personally ive been bitten by this in the past onh other accounts, and now rarely ever set the default database to anything other than master.







  3. FrankKalis Moderator

    Depending on your authentication mode, try


    OSQL -E -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"

    with Windows authentication. Respectively


    OSQL -U "Login" -P "Password" -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"

    with SQL Server authentication


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  4. surendrakalekar New Member

    quote:Originally posted by FrankKalis

    Depending on your authentication mode, try


    OSQL -E -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"

    with Windows authentication. Respectively


    OSQL -U "Login" -P "Password" -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"

    with SQL Server authentication


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

    I am trying this
    OSQL -E -S "dataserverdataserver" -d master -Q "EXEC sp_defaultdb 'sa', 'master'"
    but giving me error "Incorrect syntax near 'E'."


    Surendra Kalekar

  5. surendrakalekar New Member

    quote:Originally posted by Chappy

    restore or recreate that database using another user if possible
    then you can log in as sa and change your default database.
    Maybe theres an easier way, ive never had it happen on an sa account so I dont really know

    Personally ive been bitten by this in the past onh other accounts, and now rarely ever set the default database to anything other than master.








    Other users does not having rights to create database.


    Surendra Kalekar

  6. surendrakalekar New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Depending on your authentication mode, try<br /><pre id="code"><font face="courier" size="2" id="code"><br />OSQL -E -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"<br /></font id="code"></pre id="code"><br />with Windows authentication. Respectively<br /><pre id="code"><font face="courier" size="2" id="code"><br />OSQL -U "Login" -P "Password" -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"<br /></font id="code"></pre id="code"><br />with SQL Server authentication<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />C:&gt;osql -U username -P password -S "dataserverdataserver" -d master -Q "Exec sp_defaul<br />tdb 'sa', 'master'"<br />Default database changed.<br /><br />Thanks Frank it works. My previous try was in QA[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] that is why it was throwing error. <br /><br /><u><b>I would like to suggest all the sql server users, that never change the default database of 'sa' user.</b></u> Once again many many thanks frank.<br /><br /><br /><h6>Surendra Kalekar</h6>
  7. satya Moderator

    Keep the SA's default database to master (always) which is one of the good practice.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. FrankKalis Moderator

    Glad I could help. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  9. silvap New Member


    Hi Guys,

    I am having this error, and as requested I tried:

    OSQL -E -S "Servername" -d master -Q "EXEC sp_defaultdb 'Login', 'New default db'"; and apparently it worked because the commend displayed message 'Default Database changed'.

    ... but I still cannot open the database server, neither Enterprise manager nor QA nor ODBC connections.

    What can I try ?.
  10. satya Moderator

    Does that user login has privileges to READ the database, such as DB_DATAREADER fixed role assignemnt?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page