January 25, 2008 at 3:02 am
On single server I have two databases
1. Walkers
2. Wrbeta
there are 2 login id's
1. walkers
2. wrbeta
I am logged in or I have register on SQL server instance using walkers
Now I have changed something in stored procedure of wrbeta database.
when I am trying to save or apply these changes then it gives message
Invalid Object name ' procedure name ' .
How I solve this problem. Is there any way.
I have to update stored procedure/tables of other-WRBETA database by using walkers login of WALKERS database.
Is it possible..
Plz reply..
January 25, 2008 at 11:27 am
The error message did not show you have any permission issue but the validation of your stored procedure name. You may check it.
January 28, 2008 at 3:10 am
Actually stored procedure has been created with wrbeta login name(ownership).
But I have register/logged to database server using walkers.
Right now I m trying to change stored proc & at the time of applying changes it shows me error.
Invalid Object name.
Actually what I have to do here is - I have to change stored procedure of walkers_web (wrbeta-login name) using walkers login of Walkers database.
Is it possible ? if yes then what permission I have to give walkers login & also for vice versa condition.
Plz reply soon...
January 28, 2008 at 3:11 pm
The error message, Invalid Object Name, comes from many reasons, such as,
1. The naming is incorrect;
2. A table or a column does not exist;
3. The owner is incorrect;
...
Based on the information you provided, here is my guess.
1. The stored procedure was created by another user (A), and the stored procedure is owned by the user.
2. You login to SQL Server with account, B, and tried to modify this stored procedure. You would like to save it under the user A.
It might be your problem.
The problem is that you may not be able to save an object under other user's account.
January 29, 2008 at 3:10 am
yes u r right..
I am trying to save stored proc of wrbeta owner login using walkers login.
Is it possible to update & save stored proc with different login.
If yes plz explain how.
Otherwise I have to login with wrbeta login then have to change the stored
proc.
January 29, 2008 at 1:21 pm
Let us assume you have authority to do it.
By default, when you try to modify a stored procedure in a script, you may not see its owner. But there is.
What you need to do is to add its owner just before the name of the stored procedure. For exmaple, you have a stored procedure,
sp_test1 with ownership A
When you edit it with your account B, you may see
ALTER sp_test1 AS SELECT * FROM T1
What you need to do is add A in front of the name of the stored procedure, such as,
ALTER A.sp_test1 AS SELECT * FROM T2
The reason is that if you do not include its ownership, the SQL Server assumes that you would like to alter a stored procedure, B.sp_test1, because you login as B. But this stored procedure, B.sp_test1, does not exist.
Also, you may create your own stored procedure, such as
CREATE B.sp_test1 AS SELECT * FROM T2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply