importing tables keeping username

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

SQL Server Administration (2005)

importing tables keeping username

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-04 : 08:13:03

i'm importing tables from another sql server -
now the tables are coming is as
esther.tablename (my username on the other server)
instead of
dbo.tablename

how can i change this?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-04 : 22:33:40

with sp_changeobjectowner.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-02-04 : 22:53:10

If you have numerous objects that require an ownership change, you can use the following SQL Server stored procedure to ease the process: if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chObjOwner]
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int -- UID of the user
declare @objName varchar(50) -- Object name owned by user
declare @currObjName varchar(50) -- Checks for existing object owned by new user
declare @outStr varchar(256) -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
print 'Error: No objects owned by ' + @usrName
close chObjOwnerCur
deallocate chObjOwnerCur
return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
set @currObjName = @newUsrName + "." + @objName
if (object_id(@currObjName) > 0)
print 'WARNING *** ' + @currObjName + ' already exists ***'
set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
print @outStr
print 'go'
fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0

GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-05 : 02:56:15

ok so my imported table looks like the following

esther.useraccess

when i run

EXEC sp_changeobjectowner 'useraccess', 'dbo'

i get an error that table useraccess doesn't exist.
esther.useraccess gives the same error -- how to i access this table - i want it to be like any other database table

dewacorp.alliances

452 Posts

Posted - 2008-02-05 : 03:04:43

It's supposed to be:

EXEC sp_changeobjectowner 'esther', 'dbo'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 03:06:15

Don't confuse SCHEMA with OWNER.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-05 : 03:10:19

is that schema

how do i change it so i can access these tables?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-05 : 03:16:53

sg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 58
Object 'esther' does not exist or is not a valid object for this operation

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-02-05 : 04:23:47

it's as if the db can't see it

if i do select * from esther.useraccess
i also get invalid object name
same with select * from useraccess
how can i access these records?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-05 : 22:33:37

EXEC sp_changeobjectowner [esther.useraccess], 'dbo'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources