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

Show quoteHide quote

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.

Show quoteHide quote

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?

Show quoteHide quote

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

Show quoteHide quote

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


Show quoteHide quote

Stored procedures in an n-tiered environment.
a really bad ordering problem
Calculated fields with Days/Dates
Dangers of update statement
Automating database changes