Could not find stored procedure.....SQL 2000

We are setting up a new server to replace a currently running SQL 2000
server. After restoring the database from a backup, when we attempt to log in
using a custom application, we get an error message stating "[Microsoft][ODBC
SQL Server Driver][SQL Server]Cound not find stored procedure
'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in the
Stored Procedures subfolder in the restored database. I ran the restore from
Enterprise Manager just using the wizzard and had no issues when restoring.
Any ideas?What user are you connecting as? What is that user's default database? Are
you sure you're executing in the right database? (A common problem is that
a connection string without a database specified would default to a user's
default database, but if the database is dropped/re-created/renamed...) Who
is the owner of the stored procedure? If it is dbo, did you try EXEC
dbo.drs_AuthenticateCurrentUser instead of EXEC drs_AuthenticateCurrentUser?
It is always a good idea to prefix object names both when creating and when
executing.
--
Aaron Bertrand
SQL Server MVP
"Jason Carter" <JasonCarter@.discussions.microsoft.com> wrote in message
news:0A96F5C3-EB54-46B7-A7B2-3EBAD3E9842B@.microsoft.com...
> We are setting up a new server to replace a currently running SQL 2000
> server. After restoring the database from a backup, when we attempt to log
> in
> using a custom application, we get an error message stating
> "[Microsoft][ODBC
> SQL Server Driver][SQL Server]Cound not find stored procedure
> 'drs_AuthenticateCurrentUser'. I can see that stored procedure listed in
> the
> Stored Procedures subfolder in the restored database. I ran the restore
> from
> Enterprise Manager just using the wizzard and had no issues when
> restoring.
> Any ideas?|||Thanks for the reply Aaron....
I have tried a number of users including sa. sa's default database is
'master', but the other users I have tried have their default databases set
to MTCCashRecon which is where the stored procedure is located. I have also
verified the permissions for each of those users in that database is the same
as the ones on the old server. The owner of the stored procedure is dbo. I
have not tried to manually execute that procedure; I have only lanched this
from the application. I am nowhere near a SQL DBA and kind of got forced into
this one. I do not have access to the code and do not know if the stored proc
is being called as dbo.drs_authenticateCurrentUser or if it is being called
simply by the name; although I can say that the database was simply restored
from a .BAK file and I would have hoped that it would have just worked.
I have considered using the Data Transformation Sets to get the stored
procedures from the old server, but am not sure how that affects the old
server since it is still in use; does it lock the server or move any stored
procedures vs. just copying them.