Default database of sa user | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Default database of sa user

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
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.
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)
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
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
<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>
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.
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 />

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 ?.
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.
]]>