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
CREATE PROCEDURE [owner_name].[procedure_name]
AS
...
That’s the way I do it..
Regards,
Niels
[QUOTE=Scott]
CREATE PROCEDURE [owner_name].[procedure_name]
AS
...
That’s the way I do it..
Regards,
Niels
[/QUOTE]
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
You didn’t have to delete them I think, you could just modify them (I think)…
Regards,
Niels
[QUOTE=Scott]
You didn’t have to delete them I think, you could just modify them (I think)…
Regards,
Niels
[/QUOTE]
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
[QUOTE=Scott]
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
[/QUOTE]
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!