Change only USER tables to a new user

17 Apr 2007 3:10 PM

Tmuldoon

Hello,

I have a database created with several tables. There are two types
of table - system and user.

I want to change all the user tables ownership from dbo to another
user.

Is there a script that would allow me to do that - but only affect the
user type tables?

Thanks,

Tmuld

17 Apr 2007 5:52 PM

John Bell

Hi

Show quoteHide quote

"Tmuldoon" wrote:

> Hello,
>
> I have a database created with several tables. There are two types
> of table - system and user.
>
> I want to change all the user tables ownership from dbo to another
> user.
>
> Is there a script that would allow me to do that - but only affect the
> user type tables?
>
> Thanks,
>
> Tmuld
>

You don't give the version of SQL Server you are using! SQL 2005 has a ALTER
SCHEMA statement that will allow you to change the schema of a securable.
The tables to move could be identified by selecting them from the sys.tables
view.

If you are using SQL 2000, then I think you will need to re-create the table
and use INSERT..SELECT to move the data, you may have to drop the constraints
on the old table before creating the new table to avoid name conflicts. This
may be easier to do by scripting out the tables and editing the script.

Why do you need to change schemas?

John

17 Apr 2007 6:17 PM

Russell Fields

Tmuld,

If you are using SQL Server 2000 use: sp_changeobjectowner
If you are using SQL Server 2005 use: ALTER TABLE

You can create your own script by:

SELECT 'EXEC sp_changeobjectowner ''' +TABLE_SCHEMA+
'.'+TABLE_NAME + ''', ''NewOwner''', *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

The output of this SQL 2000 select is a script you can run. (Or edit first,
then run.)

If you use the ALTER TABLE approach then apply this idea to generating that
script. Be sure with SQL 2005 that you understand the difference between
'owner' and 'schema' since these are now separated.

RLF

Show quoteHide quote

"Tmuldoon" <tmuld***@spliced.com> wrote in message
news:1176822600.477208.87050@b75g2000hsg.googlegroups.com...
> Hello,
>
> I have a database created with several tables. There are two types
> of table - system and user.
>
> I want to change all the user tables ownership from dbo to another
> user.
>
> Is there a script that would allow me to do that - but only affect the
> user type tables?
>
> Thanks,
>
> Tmuld
>

18 Apr 2007 7:54 AM

John Bell

Hi

Show quoteHide quote

"Russell Fields" wrote:

> Tmuld,
>
> If you are using SQL Server 2000 use: sp_changeobjectowner
> If you are using SQL Server 2005 use: ALTER TABLE
>
> You can create your own script by:
>
> SELECT 'EXEC sp_changeobjectowner ''' +TABLE_SCHEMA+
> '.'+TABLE_NAME + ''', ''NewOwner''', *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> ORDER BY TABLE_NAME
>
> The output of this SQL 2000 select is a script you can run. (Or edit first,
> then run.)
>
> If you use the ALTER TABLE approach then apply this idea to generating that
> script. Be sure with SQL 2005 that you understand the difference between
> 'owner' and 'schema' since these are now separated.
>
> RLF
>
> "Tmuldoon" <tmuld***@spliced.com> wrote in message
> news:1176822600.477208.87050@b75g2000hsg.googlegroups.com...
> > Hello,
> >
> > I have a database created with several tables. There are two types
> > of table - system and user.
> >
> > I want to change all the user tables ownership from dbo to another
> > user.
> >
> > Is there a script that would allow me to do that - but only affect the
> > user type tables?
> >
> > Thanks,
> >
> > Tmuld
> >

I had forgotten about sp_changeobjectowner!

For SQL 2005 you could use

SELECT 'ALTER SCHEMA NewSchema TRANSFER [' +TABLE_SCHEMA + '].['+TABLE_NAME
+ ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

With both methods you will need to re-apply permissions to the tables.

John