change ownership to dbo

Y

yorri

Perch
Does anyone know how to change the ownership on stored procedures? I found a script but it changes all objects in the database and I cannot have that. I need certains stored procs to be changed and I cannot seem to figure it out.

If not.....

How does one rewrite the create script to specify the owner?

All my procedures are written in the script as such...

CREATE procedure spName
AS

What would I need to change.

Thanks for help in advance
 
Code: 
CREATE PROCEDURE [owner_name].[procedure_name]
AS
...

That's the way I do it..

Regards,
Niels
 
Scott said:
Code: 
CREATE PROCEDURE [owner_name].[procedure_name]
AS
...

That's the way I do it..

Regards,
Niels
Click to expand...

Thanks niels,

I decided to delete everything and edited the scripts. Took some time but it worked.

Unfortunately I had to go back in and give exec rights to my login name GRRR
 
Scott said:
You didn't have to delete them I think, you could just modify them (I think)...

Regards,
Niels
Click to expand...

but that was my original question because I don't know how to change the ownership of a stored procedure. I can't find how in enterprise manager.
 
:(

Right mouse click on the procedure, select "properties", and in the window the procedure's code should show up...
Is this what you meant?

(Sorry if you removed all your procedures because of me )

Regards,
Niels
 
Scott said:
:(

Right mouse click on the procedure, select "properties", and in the window the procedure's code should show up...
Is this what you meant?

(Sorry if you removed all your procedures because of me )

Regards,
Niels
Click to expand...

I know where the properties are but you cannot change the ownership of them this way. (Not talking about exec right, I am talking about ownership which is a different thing)

I didn't remove them because of you so don't worry. I actually got frustrated and did it before you posted your post. I thought it would be easier to just remove them and then add them again using the proper script.
 
here is the code to do it if anyone cares...

sp_changeobjectowner spname, newowner

replace spname and newonwner with the appropriate info
 
This is how I did it:

First, create a stored procedure:
CREATE PROCEDURE [dbo].[change_sp_owner] AS
exec sp_changeobjectowner 'nameofproceduretochange', 'dbo'
GO

Then run this script in Query Analyzer:

DECLARE @RC int
-- Set parameter values
EXEC @RC = [databasename].[dbo].[change_sp_owner]

----- cheers!
 
You must log in or register to reply here.
Back
Top