Using sp_changeobjectowner - 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 2000 Forums

SQL Server Administration (2000)

Using sp_changeobjectowner

Author

Topic

bilbo13
Starting Member

1 Post

Posted - 2003-07-31 : 02:04:40

I got this SQL from a post by ANTON (and modified it for my user):

SELECT 'exec sp_changeobjectowner ''' + LTRIM(u.name) + '.' + LTRIM(s.name) + ''',' + '''SMARTEAM''' AS Expr1
FROM sysobjects s INNER JOIN
sysusers u ON s.uid = u.uid
WHERE (u.name <> 'SMARTEAM') AND (s.xtype IN ('V', 'P', 'U')) AND (u.name NOT LIKE 'INFORMATION%')
ORDER BY s.name

I can execute it, and it generates the SQL that I want to run in the lower pane of the Query design window. (442 lines)

For example:
exec sp_changeobjectowner 'stadmin.TDM_DB_VERSION','SMARTEAM'

If I copy from the lower pane and paste into the SQL pane, I get an error:
The Query designer does not support the EXEC SQL construct.

Then:
[Microsoft][ODBC SQL Server Driver][SQL Server]Object 'TDM_DB_VERSION' does not exist or is not a valid object for this operation.

I'm (obviously) not a SQL server expert. How can I execute this SQL against my database?

Thanks in advance for your help!

Bill
williambryantmiller@hotmail.com

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-07-31 : 03:23:09

I think it's complaining that SMARTEAM hasn't been set up as a user/login for this database.

I can get the same error message by changing my owner name to one that doesn't exist whenever the original owner is dbo.

When the original owner is someone else (and the change to name is invalid) i get a sensible error message telling me that there's no login for that object.

When the original name is OK and the change to name is OK, pasting in the code and executing works for me...

HTH

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-07-31 : 15:23:09

Are you trying to do this from the query designer in Enterprise Manager or from Query Analyzer? You will get that exec error if you are trying to do it in the EM query designer. Paste your query into QA and it should work just fine.

Jeff Banschbach, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources