Stored Procedure Alter Command giving error?

[nmmure]

Hi,

I am trying alter my existing Stored Procudure it is giving below error.
"Server: Msg 4064, Level 11, State 1, Procedure sp_Ex_Daily, Line 87
Cannot open user default database. Login failed."

"sp_Ex_Daily" is my SP name. we have 5 database's existing in same instance. SP is existing in "Layer" Database and the same user default database is "DataWork".

I make the user default database as "Layer" still I am getting the above error. In Line 87
"UPDATE #TEMP"
Simple Temp table update statement is there.

I did small change (Changed hardcode string value "NAN" to "INAN" )in SP. I didn't get the IDEA.

Pls advise me any one have idea.

Thanks
Mure

 

[mrdenny]

The cannot open default database error means that your default doesn't either doesn't exist, is corrupt, or is being restored. Use the SQL Management tools and see what the issue with that database is.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[JackVam]

If it persists issue an ALTER LOGON statement and change the default database

All the IT jobs in one place -

http://www.ITjobfeed.com

 

[gmmastros]

ALTER LOGON is for SQL 2005.

If you are using SQL2000, then you'll want to use sp_defaultdb

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nmmure]

Thanks Denny,

User is corrupted ( I don't know why it is not working ). User droped and recreated. The script was working fine and executed without errors. Thanks

This is my user, SO, I drop the user and recreated. If user is different how can DBA will fix the issue. Is there any way to fix this issue.

Thanks for helping.

Mure

 

[mrdenny]

The DBA would do the same thing that you did. You must have sysadmin rights to the server if you were able to remove your account and recreate it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog