Change only USER tables to a new user

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
Hi
"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
|||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" <tmuldoon@.spliced.com> wrote in message
news:1176822600.477208.87050@.b75g2000hsg.googlegro ups.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
>
|||Hi
"Russell Fields" wrote:
[vbcol=seagreen]
> 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" <tmuldoon@.spliced.com> wrote in message
> news:1176822600.477208.87050@.b75g2000hsg.googlegro ups.com...
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