February 14, 2008 at 5:37 pm
Hello Everyone
I am trying to change the ownership of all objects of a certain type, such as all the Views to another user.
I am using the old slow way of using the command sp_changedatabaseobject
What I would like is if anyone has a way to change them all at once. I have tried to simply run an update query on the sysobjects table. SQL would not allow me to do that.
Does anyone have a script to share that can do this in a faster way.
Thanks
Andrew
February 14, 2008 at 8:17 pm
I've always used a cursor to dynamically create a SQL statement that will change object ownership. Unfortunately I can not give you that query (PTO and no access to my script repository) but I did find this script at microsoft.com and you should easily be able to mod it. The one here changes all object for a given owner. By changing the cursor to query sysobjects where type = '?' where ? is the letter designation for the object type you're looking to change. I would suggest adding this to the where clause of the cursor, so that you're changing all object of a given type owned by a specific schema.
http://support.microsoft.com/kb/275312
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 15, 2008 at 12:30 am
In fact, schemas are menth to avoid this kind of actions.
SQL2005 wright ?
Schema should be a container for logicaly related objects.
check out ALTER SCHEMA schema_name TRANSFER securable_name
and ALTER AUTHORIZATION
ON [ :: ] entity_name
TO { SCHEMA OWNER | principal_name }
ps Also keep in mind that you should also change the object-definition (view/sp/function,..) itself
because it may ( should ! ) have qualified objects in use.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2008 at 10:10 am
Hi,
Check out the below link. This has the script to change all the object owners.
http://johnnynine.com/blog/HowToChangeTheOwnerOfAllTablesInADatabase.aspx
Thanks -- VJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply