How can I change a table/view owner?

Hi,
We have a database in which we would like to change the owner of certain
tables and views. Can someone help me to do this? If there is a script
that I can use then that would be perfect. Thanks in advance.
With best regards,
Parimal
Email: Parimal.Jani@.hp.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sp_changeobjectowner
"PARIMAL JANI" wrote:

> Hi,
> We have a database in which we would like to change the owner of certain
> tables and views. Can someone help me to do this? If there is a script
> that I can use then that would be perfect. Thanks in advance.
> With best regards,
> Parimal
> Email: Parimal.Jani@.hp.com
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

How are views used each time it is requested.

Taking the view below, is a view reran each time it is requested or is there
some sort of caching that would hold the dataset ready for the next request
of the view?
CREATE VIEW dbo.vwTodaysNeurologyAppts
AS
SELECT TOP 100 PERCENT PatientName, ResourceCode, ApptDate, ActivityType
FROM dbo.Appointment
WHERE (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate,
GETDATE()) = 0) AND (ApptStatus = 'ATTENDED')
ORDER BY ApptDateNo caching of the dataset, as that would be incredibly complex considering t
hat the underlying data
might have been modified in between. The data that has been accessed by the
query you ran against
the view can of course be cached (just as if you were querying those tables
directly).
You can create an index on a view (under certain circumstances) which essent
ially materializes the
view. Any modification if the underlying data need to be propagated to the m
aterialized/indexed
view.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OfR%23iKDKGHA.2992@.tk2msftngp13.phx.gbl...
> Taking the view below, is a view reran each time it is requested or is the
re some sort of caching
> that would hold the dataset ready for the next request of the view?
>
> CREATE VIEW dbo.vwTodaysNeurologyAppts
> AS
> SELECT TOP 100 PERCENT PatientName, ResourceCode, ApptDate, ActivityTy
pe
> FROM dbo.Appointment
> WHERE (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate, GE
TDATE()) = 0) AND
> (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>|||no, the results are not cached between executions - but the source data
pages are, and depending on the level of activity on the server, there is a
good chance that they will still be in memory on successive executions.
however, you can persist the view by creating a clustered index on it - see
"indexed views" in BOL.
btw, don't rely on "SELECT TOP 100 PERCENT" and "ORDER BY" inside a view.
ORDER BY should be specified in the final SELECT. also, if there is an index
on ApptDate column, don't use DATEDIFF on it because the index won't be
used.
dean
"CD" <mcdye1@.hotmail.REMOVETHIS.com> wrote in message
news:OfR%23iKDKGHA.2992@.tk2msftngp13.phx.gbl...
> Taking the view below, is a view reran each time it is requested or is
> there some sort of caching that would hold the dataset ready for the next
> request of the view?
>
> CREATE VIEW dbo.vwTodaysNeurologyAppts
> AS
> SELECT TOP 100 PERCENT PatientName, ResourceCode, ApptDate,
> ActivityType
> FROM dbo.Appointment
> WHERE (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate,
> GETDATE()) = 0) AND (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>|||Each SELECT statement performed against the view will result in a SELECT
statement against the base tables. Data is cached in memory to make
subsequent SELECTS against the view faster, but the actual view data is
stored only in the base tables unless you create a clustered index on the
view. Indexes with views, also known as materialized views, have a seperate
data store for the columns in the view within the clustered index.
If you wanted to index your view, you would have to remove the TOP clause,
which would also necessitate removing the ORDER BY clause.
"CD" wrote:

> Taking the view below, is a view reran each time it is requested or is the
re
> some sort of caching that would hold the dataset ready for the next reques
t
> of the view?
>
> CREATE VIEW dbo.vwTodaysNeurologyAppts
> AS
> SELECT TOP 100 PERCENT PatientName, ResourceCode, ApptDate, ActivityTy
pe
> FROM dbo.Appointment
> WHERE (LEFT(ResourceGroup, 3) = 'NEU') AND (DATEDIFF(Day, ApptDate,
> GETDATE()) = 0) AND (ApptStatus = 'ATTENDED')
> ORDER BY ApptDate
>
>

Hosting DBO Nightmare going Live in 3 Days and counting

My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.

I've used a few sql scripts to change the owner using

Exec sp_changeobjectowner 'object', 'user'

and I've used

Grant Execute on object to user

These have changed ownership but the site still doesn't work

Does anyone know what to do to achieve this.

Any help greatly appreciated

Many Thanks

DuncanI hate to tell you, but our hosting operation does give our customers only DDLAdmin rights...

...and they have NO (!) problem creating entries belonging to the dbo user.

It is trivial. Just make sure you create them for the dbo user in the first place (i.e. EXPLICIT).|||Thanks Thona, this seems to set all the tables up fine but when I run dotnetnuke, it has a index out of bound error, I think this is happening because it is not creating any records in the table