sp_changeobjectowner question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_changeobjectowner question

Author

Topic

alpoor
Starting Member

29 Posts

Posted - 2007-02-08 : 17:51:17

Can I change owner of another database table from different database on the same server.

Let's say, I have two databases d1 and d2, I am trying to run following from d1. Is it possible?

sp_changeobjectowner 'd2.x.table1' 'y'

changing ownership 'x' to 'y' on d2 database from d1 database in query analyzer

Basically I want to run same script on all databases at one shot istead of logging on to each running the same script

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 03:10:26

You can prefix database name before SP to have it:

d2..sp_changeobjectowner 'table1' 'y'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

alpoor
Starting Member

29 Posts

Posted - 2007-02-09 : 13:48:55

Thx for reply. What I was trying to see whether I can change ownership of all tables in all databases on the same server. I found the following script on internet. The problem here is how can I make this script work in all databases by submitting one shot

DECLARE @oldowner sysname, @newowner sysname, @sql varchar(1000)

SELECT
@oldowner = 'OLD_OWNER',
@newowner = 'dbo',
@sql = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @oldowner + '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @newowner + ''''

EXECUTE sp_MSforeachtable @sql

Kristen
Test

22859 Posts

Posted - 2007-02-10 : 08:55:19

Continued over at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78907

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources