mass ownership changes to stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

mass ownership changes to stored procedures

Author

Topic

aspfree
Starting Member

3 Posts

Posted - 2002-10-13 : 14:58:21

i want to do a mass change of ownership on all stored procedures from myId.storeproc to dbo.storedproc. I found this script and appears to work but when i refresh the list, the ownership doesn't change.
What is showed in query analyzer

Exec sp_changeobjectowner '[aspfree].[storedproc]','dbo'
this shows up on all objects but nothing gets changed. The id i'm loggin in as has dbo rights on the db.

here is the script i'm trying to use.

DECLARE @oldOwner sysname, @newOwner sysname
SELECT @oldOwner = 'aspfree'
, @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0

aspfree
Starting Member

3 Posts

Posted - 2002-10-13 : 15:30:16

Does this have to be run as SA or someone with SA type perm's?

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-10-13 : 16:54:58

From BOL:

quote:
Only members of sysadmin fixed server role, the db_owner fixed database role, or a member of both the db_ddladmin and db_securityadmin fixed database roles can execute sp_changeobjectowner.

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-10-13 : 18:28:37

aspfree, the script you are using is only "generating" the required commands to change the ownership. You need to copy the output, and paste in a separate Query Analyzer window and run it. Only then will your stored procedure owners change.

--
HTH,
Vyas
http://vyaskn.tripod.com

aspfree
Starting Member

3 Posts

Posted - 2002-10-13 : 21:28:58

Makes perfect sense now. I'm no professional DBA, thanks for pointing that out. Now I know.

daviddeldave
Starting Member

28 Posts

Posted - 2003-04-02 : 10:41:42

Does anyone know why INFORMATION_SCHEMA.ROUTINES does not exist in my database?

David Mercer

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-02 : 11:49:48

It should exist in master but be accessible from your DB.

If not maybe you are in 6.5 compatibility mode?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

daviddeldave
Starting Member

28 Posts

Posted - 2003-04-03 : 05:29:01

Thanks, I still cant find it. We are using ver 7.0 here is the results of running aspfree code above.

DECLARE @oldOwner sysname, @newOwner sysname
SELECT @oldOwner = 'david_mercer'
, @newOwner = 'dbo'
select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+''''
from
INFORMATION_SCHEMA.ROUTINES a
where
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @oldOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0

Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'INFORMATION_SCHEMA.ROUTINES'.

Does Information_Schema.routines not exist in Ver 7.0

David Mercer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources