Table ownership
-
Interesting, now I have row information using your last query.
The wyantetest is listed in the Table_Schema for changetableowner
Table_Name. However I still receive a "Invalid object name" when
I launch a Select * From changetableowner. Ideas?
R.,
Ed
-
We crossed e-mails, I'll try your last message.
-
I believe your on to something. Your last query returns rows of 5 objects, two of them table objects that a select * from returns
"invalid object" One being changetableowner which has an xtype of U.
I did type the case as you indicated, and the USE DB command.
Colation on both servers is SQL_Latin1_General_CP1_CI_AS
R.,
Ed
-
You may have to use full ownership to access your table, such as, [owner].tablename
-
Ed
In Query Analyzer, change to Results in Text and run the following query:
SELECT 'SELECT TOP 10 * FROM ' + USER_NAME(uid) + '.' + [name]
FROM sysobjects WHERE xtype = 'U'
Then copy the result set into your query window and execute that. Does that give any invalid objects?
John
-
Is it possible that you changed the table ownership to an orphaned user? I don't know if sp_changeobjectowner checks for this.
Try running "sp_change_users_login @Action = 'Report' "
-
Scott,
The procedure returns no rows. Something also interesting is
after deleting the table, the sysobjects table in
the master DB indicates the table is still registered,
but not registered in sysobjects table of the
DB where the table was created. Is this the way the
system manages object registration?
Thanks to everyone for helping me with their ideas.
R.,
Ed
-
If the table appears in the master..sysobjects table, it was created in the master database. This may not have been your intention, but that's what happened.
-
You're correct Scott, in my haste I created the table in both
master and test dbs. So I started from scratch again, created a table
as dbo owner, successfully changed the owner to wyantetest
with the sp_changeobjectowner. However when I attempt to
change the table owner back to dbo the error message is returned
the "object (table) does not exist or is not a valid object for this operation". I'm entering the arguments (object and owner) exactly
as they appear in the sysobjects and sysusers tables.
???
R.,
Ed
-
Here's a full script that creates a new users, adds him to the sa role and grant him access to the database. Then it show how the sp_rename works.
At this point I have no idea of what is going wrong in your db, but I hope this may help you understand more what is going on.
USE master
GO
sp_revokedbaccess @name_in_db = N'UsrName'
GO
sp_droplogin @loginame = N'UsrName'
GO
sp_addlogin @loginame = N'UsrName',
@defdb = N'master'
GO
sp_addsrvrolemember @loginame = N'UsrName',
@rolename = N'sysadmin'
GO
sp_grantdbaccess @loginame = N'UsrName',
@name_in_db = N'UsrName'
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Test' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.Test
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'Test' AND XType = 'U' AND USER_NAME(uid) = 'UsrName')
DROP TABLE UsrName.Test
GO
CREATE TABLE dbo.Test
(
ID INT NOT NULL
)
GO
INSERT INTO dbo.Test (ID) VALUES (2)
SELECT * FROM dbo.Test
GO
EXEC sp_changeobjectowner 'dbo.Test', 'UsrName'
GO
SELECT * FROM UsrName.Test
GO
EXEC sp_changeobjectowner 'UsrName.Test', 'dbo'
GO
SELECT * FROM UsrName.Test
SELECT * FROM dbo.Test
GO
sp_droplogin @loginame = N'test'
GO
DROP TABLE dbo.Test
GO
-
Also have you run any dbcc command to check the integrity of the database?
-
I don't believe you've posted the exact commands you've been using that are causing you problems. This script runs successfully on one of my SQL 2000 servers, and shows the owner change from dbo to wyantetest and back to dbo.
CREATE
TABLE dbo.TableOwnerTest (a INT, b INT)
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableOwnerTest'
EXEC sp_changeobjectowner 'dbo.TableOwnerTest', wyantetest
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableOwnerTest'
EXEC sp_changeobjectowner 'wyantetest.TableOwnerTest', dbo
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableOwnerTest'
DROP TABLE dbo.TableOwnerTest
-
The script below was pulled from sp_changeobjectowner specifically for the error you are receiving. Substitute your owner.tablename where indicated. This should show you where the 'failure' is.
declare
@objid int,
@objname nvarchar(517)
set @objname = '[owner].
' -- sub your table here
-- from sp_changeobjectowner
/*
select @objid = object_id(@objname, 'local')
if (@objid is null) OR
(select parent_obj from sysobjects where id = @objid) <> 0 OR
ObjectProperty(@objid, 'IsMSShipped') = 1 OR
ObjectProperty(@objid, 'IsSystemTable') = 1 OR
ObjectProperty(@objid, 'ownerid') in (0,3,4) OR --public, INFORMATION_SCHEMA, system_function_schema
-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
EXISTS (SELECT * FROM sysdepends d WHERE
d.depid = @objid -- A dependency on this object
AND d.deptype > 0 -- that is enforced
AND @objid <> d.id -- that isn't a self-reference (self-references don't use object name)
AND @objid <> -- And isn't a reference from a child object (also don't use object name)
(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
)
begin
-- OBJECT NOT FOUND
raiserror(15001,-1,-1,@objname)
return 1
end
*/
-- above modified to show which is the culprit
select @objid = object_id(@objname, 'local')
if (@objid is null)
print 'ObjectID is NULL'
if (select parent_obj from sysobjects where id = @objid) <> 0
print 'It is a child object'
if ObjectProperty(@objid, 'IsMSShipped') = 1
print 'It is MSShipped'
if ObjectProperty(@objid, 'IsSystemTable') = 1
print 'It is a system table'
if ObjectProperty(@objid, 'ownerid') in (0,3,4)
print 'It is owned by public, INFORMATION_SCHEMA or system_function_schema'
-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
if EXISTS (SELECT * FROM sysdepends d WHERE
d.depid = @objid -- A dependency on this object
AND d.deptype > 0 -- that is enforced
AND @objid <> d.id -- that isn't a self-reference (self-references don't use object name)
AND @objid <> -- And isn't a reference from a child object (also don't use object name)
(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
)
print 'Dependencies exist'
HTH
-
You people are the greatest! Da-aah, I’m such a dummy. I missed the fact when you guys (gals too) say to use full ownership to access the table, it
means FULL access name, including domain ie. HBOFFICE\WYANTETEST.
TableOwnerTest. Again thanks to everyone for you generous time and
scripts to help solve this problem.
R.,
Ed
-
That's why I love to see and use scripts
.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply