Re: Failed to copy table objects - DTS Wizard
From: Paula (paula.potter@telequip.co.uk)
Date: 04/23/03
- Next message: Madhu: "Re: SQL-Server and Content encryption"
- Previous message: Andrew J. Kelly: "Re: SQL-Server and Content encryption"
- In reply to: Dan Guzman: "Re: Failed to copy table objects - DTS Wizard"
- Next in thread: Dan Guzman: "Re: Failed to copy table objects - DTS Wizard"
- Reply: Dan Guzman: "Re: Failed to copy table objects - DTS Wizard"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Paula" <paula.potter@telequip.co.uk> Date: Wed, 23 Apr 2003 06:53:02 -0700
Read this article - and a couple of other similar ones -
everything I've read seems to point back to the same
thing - need to change ownership of the databases to login
with no corruption.
Your script implies that I only need to change the BD
ownership to a temporary login then back again to sa -
will this resolve the user mismatch issues for sa?
I am worried about changing the DB owner as I'm not sure
what knock-on effects there may be.
I know it's very poor practice, but a lot of stuff access
the databases as sa; EM, Query Analyzer, MSAccess DB's,
scheduled jobs etc.
Can you offer any advise of common failures after changing
DB ownership? Things to check for?
>-----Original Message-----
>The DMO error may be due to a database ownership problem
that can occur
>when a database is restored or attached. Try changing
the database
>owner as desired using the following script:
>
>Use MyDatabase
>EXEC sp_addlogin 'TempOwner'
>EXEC sp_changedbowner 'TemoOwner'
>EXEC sp_changedbowner 'sa'
>EXEC sp_droplogin 'TempOwner'
>GO
>
>Also, see
><http://support.microsoft.com/default.aspx?scid=kb%3ben-
us%3b305711>.
>
>--
>Hope this helps.
>
>Dan Guzman
>SQL Server MVP
>
>-----------------------
>SQL FAQ links (courtesy Neil Pike):
>
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>-----------------------
>
>"Paula" <paula.potter@telequip.co.uk> wrote in message
>news:044e01c3096c$7ccbb9f0$a601280a@phx.gbl...
>> Thanks for your response.
>>
>> I am only trying to copy a couple of table objects from
>> one SQL 2000 database to another - both on the same
>> server. No application roles set up.
>>
>> I am using Enterprise Manager which is using SQL
>> Authentication and logging in as "sa".
>> When I go to "Security" and double click on "sa" I get
the
>> following error:
>>
>> Microsoft SQL-DMO
>> Error 21776: [SQL-DMO]The name 'dbo' was not found in
the
>> Users collection. If the name is a qualified name, use
[]
>> to separate various parts of the name, and try again.
>>
>> It then lets me look at the properties and I can see
that
>> sa is a member of Server Role "System Administrators"
>>
>> Looked up error 21776 on TechNet - article 305711 talks
>> about dbo user's SID in [sysusers] table not matched
with
>> the SID in [syslogins] table - but I can't find a
>> [syslogins] table at all, only [sysxlogins] - is this
the
>> same thing?
>>
>> Running Exec sp_change_users_login 'REPORT' brings up no
>> results.
>>
>> Ran following TSQL in Query Analyzer:
>> use DBName
>> GO
>> select u.[name] AS UserName, s.[name] As MatchedLogin
>> from sysusers u
>> left join master.dbo.sysxlogins s
>> on u.sid = s.sid
>> where u.[name] = 'dbo'
>> GO
>> Got expected result with Null in MatchedLogin, but how
do
>> I fix this?
>> Tried following TSQL in Query Analyzer:
>> sp_change_users_login @Action = 'Update_One'
>> ,@UserNamePattern = 'sa'
>> ,@LoginName = 'dbo'
>> but then get following error:
>> Server: Msg 15291, Level 16, State 1, Procedure
>> sp_change_users_login, Line 88
>> Terminating this procedure. The User name 'sa' is absent
>> or invalid.
>>
>> HELP!!!!!! It's all getting very frustrating !!!!
>>
>> >-----Original Message-----
>> >Administrator does not appear anywhere in SQL Server
>> >unless you add it. The admin accounts are sa (login)
and
>> >dbo (database user).
>> >See if there is an Application Role in the database you
>> >are copying - these will always fail because you cannot
>> >import their passwords. make sure if you are copying
>> >objects that you do not copy database users and roles
if
>> >this is the case.
>> >
>> >>-----Original Message-----
>> >>When using SQL Enterprise Manager "DTS Import/Export
>> >>Wizard" to copy table objects from one SQL2000
database
>> >to
>> >>another on the same server the DTS fails with
>> >>message "Failed to copy objects from Microsoft SQL
>> Server
>> >>to Microsoft SQL Server" then when I double click on
the
>> >>failed line I have error message "[Microsoft][ODBC SQL
>> >>Server Driver][SQL Server]User or role 'Administrator'
>> >>does not exist in this database."
>> >>Administrator is surely built in and exists in all
>> >>databases?
>> >>The tables were created and are owned by DBO.
>> >>I am using SQL Authentication with sa account and
>> >password.
>> >>This worked fine last month and nothing should have
been
>> >>changed since then (or at least no-one is owning up to
>> >>changing anything!)
>> >>Please can anyone help or at least point me in the
right
>> >>direction?
>> >>.
>> >>
>> >.
>> >
>
>
>.
>
- Next message: Madhu: "Re: SQL-Server and Content encryption"
- Previous message: Andrew J. Kelly: "Re: SQL-Server and Content encryption"
- In reply to: Dan Guzman: "Re: Failed to copy table objects - DTS Wizard"
- Next in thread: Dan Guzman: "Re: Failed to copy table objects - DTS Wizard"
- Reply: Dan Guzman: "Re: Failed to copy table objects - DTS Wizard"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
