SP_changeobjectowner
May 3, 2001
Has anyone had problems with this SP? I am trying to change the owner of a stored procedure so I do the following:
sp_chaneobjectowner stored_procedure_name, dbo
(note: I have tried putting the object and new owner in single quotes, double quotes, brackets but still get same error message)
The message I get is that the object does not exist or is not a valid object for this operation.
However, it is a valid stored procedure. I see it there and can execute it. The only problem is that it has the wrong owner. Sure I can delete and recreate it, but I would like to figure out why this system SP is not working properly or what I am doing wrong.
Any suggestions would be appreciated
View 2 Replies
Oct 12, 2001
Can anyone help me with the code to change the name of the owner of all the tables in a database from @user1 to @user2 using sp_changeobjectowner in a loop with the help of system tables probably?? Thanks.
Di.
View 1 Replies
View Related
Jul 20, 2005
How do you use sqldmo object to execute system stored procedures? Anysuggestions would be appreciated.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 20, 2005
This is more of a fyi than a question.After restoring a live db backup to our development server there wasan identically named login Melb02 on both the db server and in therestored db backup.However if you log in as Melb02 on the server you cannot access anyobjects owned by Melb02 on the restored db, without puttingMelb02.<object_name> in front. This would break our app so we neededa workarround.The mssql docs say that first check if the login is the owner, thendbo then deny access - something to that effect. So this wasconfusing.workarroundI think what happened was that the db restored from the live remoteserver had a different security id (sid) to the development serveralthough both users had the same name: Melb02. Thus if you log in asMelb02 you can't get access to Melb02 objects on the restored dbbecause of the different sid.we wrote a cursor that went though everything in sysobjects belongingto Melb02 and did a : sp_changeobjectowner to a new user login thatour app now happily uses.Jol.
View 1 Replies
View Related