Monday, March 19, 2012

Changing SP Owner

I maybe using this command incorrectly but here goes:
exec sp_changeobjectowner 'usp_get_blindedversion', 'dbo'.
I want to make this stored procedure created by a developer to owner=dbo.
I cannot get this to execute, the error is "Object usp_get_blindedversion
does not exist or is not a valid object for this operation.you probably need the full path of the sp, i.e.
developerslogin.usp_get_blindedversion
A
"Rich" wrote:

> I maybe using this command incorrectly but here goes:
> exec sp_changeobjectowner 'usp_get_blindedversion', 'dbo'.
> I want to make this stored procedure created by a developer to owner=dbo.
> I cannot get this to execute, the error is "Object usp_get_blindedversion
> does not exist or is not a valid object for this operation.
>|||Hmmmm, so you are thinking the developer's login should be included at the
begining of the name of the stored procedure, let me try that.
"bagman3rd" wrote:
> you probably need the full path of the sp, i.e.
> developerslogin.usp_get_blindedversion
> A
> "Rich" wrote:
>|||This works now however I see this
"Caution: Changing any part of an object name could break scripts and stored
procedures."
This appears to be just a warning, is that correct?
"bagman3rd" wrote:
> you probably need the full path of the sp, i.e.
> developerslogin.usp_get_blindedversion
> A
> "Rich" wrote:
>|||Rich,
You should check dependencies before changing the owner. If you are calling
this sp from others sps or client code, using owner_name.sp_name then after
changing the owner those calls will fail.
Example:
create procedure [test].[p1]
as
select 1 as c1
go
create procedure dbo.p2
as
exec [test].[p1]
go
exec dbo.p2
go
exec sp_depends '[test].[p1]'
go
exec sp_changeobjectowner '[test].[p1]', 'dbo'
go
exec dbo.p2
go
drop procedure [dbo].[p2], [dbo].[p1]
go
Result:
c1
--
1
(1 row(s) affected)
In the current database, the specified object is referenced by the following
:
name
name type
-- --
dbo.p2 stored procedure
Caution: Changing any part of an object name could break scripts and stored
procedures.
Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'test.p1'.
AMB
"Rich" wrote:
> This works now however I see this
> "Caution: Changing any part of an object name could break scripts and stor
ed
> procedures."
> This appears to be just a warning, is that correct?
> "bagman3rd" wrote:
>|||Hi Rich
Object names are not necessarily unique, so if you don't specify an owner
for the proc, SQL Server won't be able to find it.
HTH
Kalen Delaney
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:25743607-5F7A-4BF0-B92E-161947AFCD03@.microsoft.com...
>I maybe using this command incorrectly but here goes:
> exec sp_changeobjectowner 'usp_get_blindedversion', 'dbo'.
> I want to make this stored procedure created by a developer to owner=dbo.
> I cannot get this to execute, the error is "Object usp_get_blindedversion
> does not exist or is not a valid object for this operation.
>

No comments:

Post a Comment