?
Solved

change owner on multiple views

Posted on 2006-05-12
6
Medium Priority
?
290 Views
Last Modified: 2008-03-17
I have found code that will loop through all tables and change the owner, but can someone provide the code that would loop through all views and change owners?
0
Comment
Question by:wppiexperts
  • 4
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16668410
DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN('U','V')
ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''dbo''')
FETCH NEXT FROM CurDbObjects INTO @dbObject END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16668417
the above code will change the ownership of all objects to 'dbo'
0
 

Author Comment

by:wppiexperts
ID: 16668560
does the above code allow me to search for a specific owner and change it to something else? I don't want something that will do a global change because some dbo owned objects need to stay that way.

(sorry that I wasn't clear on that)

also, I did run the code and changed the first instance of dbo to the owner name I wanted changed and then changed the second instance of dbo to what I wanted changed to, unfortunately, none of the views were affected by this.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 16668600
DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'      -- will search for objects owned by other than dbo,
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN('U','V')
ORDER BY so.NAME
0
 

Author Comment

by:wppiexperts
ID: 16668708
can tables be included as well through the xtype list? if so, what is the code for that?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16687702
wppiexperts,
> n tables be included as well through the xtype list?
Yes, Its already included in the statement
'U' -> User table
'V' -> View
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question