Problem with SA account

Hi, all experts here,

Thank you very much for your kind attention.

I encountered a problem with SA account log on. This problem occured after I detached one of the database, and when I tried to connect to the server with SA account, the problem occured, the error message is: Cannot open user default database. Login failed. Login failed for user 'sa'. (Microsoft SQL Server, Error:4064)

I then logged on to the server with windows authentification as administrator of the machine, and I have changed the password for SA account as my new password, but when I tried to log on as SA with my new password, the problem still remained.

Would please any experts here give me som advices for why is that and what else could I try to figure it out?

Thank you very much and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

To answer my own question, I logged on as Windows authentification, and change the password for SA account via its properties dialogue, it works fine now.

But still not sure why it did not work when I executed the query to change its password with statement: alter login sa with password='new password' unlock.

Would any experts here know what is the problem with the above statement? Thanks a lot and it is much appreciated to hear from you for that. Thanks a lot.

With best regards,

Yours sincerely,

|||

Why because , each login has a defualt database... if that database is detached/deleted/offline/recovery mode then that login will not be able to login. Generally, you should never keep a user database as default database of SA. keep some system database as default database. Let me tell u , this was nothing related to Password.

Madhu

|||

Madhu is correct. You can change the default database using ALTER LOGIN. Please refer to BOL on ho wto use this statement: http://msdn2.microsoft.com/en-us/library/ms189828.aspx.

Let us know if you have any further question, we will be glad to help.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi, thank you all for your very kind advices. It's been very helpful.

With best regards,

Yours sincerely,

Problem with SA account

Hi, all experts here,

Thank you very much for your kind attention.

I encountered a problem with SA account log on. This problem occured after I detached one of the database, and when I tried to connect to the server with SA account, the problem occured, the error message is: Cannot open user default database. Login failed. Login failed for user 'sa'. (Microsoft SQL Server, Error:4064)

I then logged on to the server with windows authentification as administrator of the machine, and I have changed the password for SA account as my new password, but when I tried to log on as SA with my new password, the problem still remained.

Would please any experts here give me som advices for why is that and what else could I try to figure it out?

Thank you very much and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

To answer my own question, I logged on as windows athentification, change the password for SA account via LOGINs properties. It works fine now.

|||

The reason that it worked when you changed it in the GUI is because when you opened the edit account window in the GUI you also changed the default database.

To get around this in the future log into the SQL Server with osql (the command line tool) and specify the -d flag with a name of a database such as master and you can get connected. You can then change the default database using the sp_defaultdb procedure or ALTER LOGIN (if SQL 2005).

|||

Hi, Mrdenny,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,