Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to renam
e
a stored procedure the name changes in the GUI, but the actually name used t
o
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actuall
y
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed
it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored pr
ocedure".
My guess is that you have procs with same name but different owners. Check s
ysobjects.
Also, be aware that sp_rename does not change the source code for the proced
ure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to ren
ame
> a stored procedure the name changes in the GUI, but the actually name used
to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editin
g
> you can see that in the code the stored procedure name has not been change
d.
> Whereas if you rename a view of function the name within the code is actua
lly
> changed.
> Can anyone explain this behavior?sql

Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to rename
a stored procedure the name changes in the GUI, but the actually name used to
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actually
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored procedure".
My guess is that you have procs with same name but different owners. Check sysobjects.
Also, be aware that sp_rename does not change the source code for the procedure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to rename
> a stored procedure the name changes in the GUI, but the actually name used to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editing
> you can see that in the code the stored procedure name has not been changed.
> Whereas if you rename a view of function the name within the code is actually
> changed.
> Can anyone explain this behavior?

Removing access to see Stored Proc contents.

Howdy all. I have a 3rd party vendor (sort of) that needs to know a login and
password on a SQL server for a DB he will be accessing. He only needs to have
the ability to exec Stored Procs (SP), not write directly to tables. The
problem that Im having with this though is that I:
1; Create a login and user named Test.
2; Add Test to a DB, but assign no permissions.
3; Login as test using Enterprise Manager.
4; I can see the names of the tables.
5; If theres a way around that, Test can also open up the SP's and see the
table names there.
So now Test has a valid login/ password and knows the names of all my
tables. Is there a way to stop him from being able to see the table names for
both #4 and #5? Or any other way Im not thinking of?
TIA, ChrisR.Not with SQL 2000 but with 2005 you can.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9FD0361F-6D5F-4A1F-B93B-7A7EB6EC651B@.microsoft.com...
> Howdy all. I have a 3rd party vendor (sort of) that needs to know a login
> and
> password on a SQL server for a DB he will be accessing. He only needs to
> have
> the ability to exec Stored Procs (SP), not write directly to tables. The
> problem that Im having with this though is that I:
> 1; Create a login and user named Test.
> 2; Add Test to a DB, but assign no permissions.
> 3; Login as test using Enterprise Manager.
> 4; I can see the names of the tables.
> 5; If theres a way around that, Test can also open up the SP's and see the
> table names there.
> So now Test has a valid login/ password and knows the names of all my
> tables. Is there a way to stop him from being able to see the table names
> for
> both #4 and #5? Or any other way Im not thinking of?
>
> TIA, ChrisR.

Removing access to see Stored Proc contents.

Howdy all. I have a 3rd party vendor (sort of) that needs to know a login an
d
password on a SQL server for a DB he will be accessing. He only needs to hav
e
the ability to exec Stored Procs (SP), not write directly to tables. The
problem that Im having with this though is that I:
1; Create a login and user named Test.
2; Add Test to a DB, but assign no permissions.
3; Login as test using Enterprise Manager.
4; I can see the names of the tables.
5; If theres a way around that, Test can also open up the SP's and see the
table names there.
So now Test has a valid login/ password and knows the names of all my
tables. Is there a way to stop him from being able to see the table names fo
r
both #4 and #5? Or any other way Im not thinking of?
TIA, ChrisR.Not with SQL 2000 but with 2005 you can.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9FD0361F-6D5F-4A1F-B93B-7A7EB6EC651B@.microsoft.com...
> Howdy all. I have a 3rd party vendor (sort of) that needs to know a login
> and
> password on a SQL server for a DB he will be accessing. He only needs to
> have
> the ability to exec Stored Procs (SP), not write directly to tables. The
> problem that Im having with this though is that I:
> 1; Create a login and user named Test.
> 2; Add Test to a DB, but assign no permissions.
> 3; Login as test using Enterprise Manager.
> 4; I can see the names of the tables.
> 5; If theres a way around that, Test can also open up the SP's and see the
> table names there.
> So now Test has a valid login/ password and knows the names of all my
> tables. Is there a way to stop him from being able to see the table names
> for
> both #4 and #5? Or any other way Im not thinking of?
>
> TIA, ChrisR.

Removing access to see Stored Proc contents.

Howdy all. I have a 3rd party vendor (sort of) that needs to know a login and
password on a SQL server for a DB he will be accessing. He only needs to have
the ability to exec Stored Procs (SP), not write directly to tables. The
problem that Im having with this though is that I:
1; Create a login and user named Test.
2; Add Test to a DB, but assign no permissions.
3; Login as test using Enterprise Manager.
4; I can see the names of the tables.
5; If theres a way around that, Test can also open up the SP's and see the
table names there.
So now Test has a valid login/ password and knows the names of all my
tables. Is there a way to stop him from being able to see the table names for
both #4 and #5? Or any other way Im not thinking of?
TIA, ChrisR.
Not with SQL 2000 but with 2005 you can.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9FD0361F-6D5F-4A1F-B93B-7A7EB6EC651B@.microsoft.com...
> Howdy all. I have a 3rd party vendor (sort of) that needs to know a login
> and
> password on a SQL server for a DB he will be accessing. He only needs to
> have
> the ability to exec Stored Procs (SP), not write directly to tables. The
> problem that Im having with this though is that I:
> 1; Create a login and user named Test.
> 2; Add Test to a DB, but assign no permissions.
> 3; Login as test using Enterprise Manager.
> 4; I can see the names of the tables.
> 5; If theres a way around that, Test can also open up the SP's and see the
> table names there.
> So now Test has a valid login/ password and knows the names of all my
> tables. Is there a way to stop him from being able to see the table names
> for
> both #4 and #5? Or any other way Im not thinking of?
>
> TIA, ChrisR.