dbo login name wrong after copy

Just copied all databases, from Windows NT4 with SQL 7 in a domain to a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database wizard.
Now a few databases still have a dbo with login name DOMAINNAME/Administrator while this domain doesn't exist anymore in a few weeks from now
1. Can I get a problem when I don't resolve this
2. Is there a way to give the dbo the sa login name"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to a
new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database
wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a few
weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
First off, 'dbo' is a database role, not a user. A role is like a group in
Windows. Anyone you assign to the dbo role in a database can do anything
they want in that database.
'sysadmin' is a server role that can do anything anywhere in your SQL server
(and so is like a super dbo, if you like). By default, the local admin
group and the user sa are both assigned to the sysadmin role, and so can
both act as the dbo of a database (plus additional actions). So there is no
need to add sa to the dbo as it is already more powerful than dbo.
Any other user that you want to have dbo access just add them to the
appropriate database's dbo role. If your DOMAIN/Administrator is going,
then leaving the login there won't be a problem, as no-one will be able to
use it (though for completeness you might want to remove it). You can add
as many other accounts as you feel is safe to the dbo role.
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003|||> First off, 'dbo' is a database role, not a user.
Actually 'dbo' is a database user and is required in every SQL Server
database. Unlike other database users, the login mapping for the 'dbo'
user is determined by database ownership.
This should not be confused with user membership of the db_owner fixed
database role. Members of the db_owner role have similar permissions as
the 'dbo' role but are not mapped to the 'dbo' user. Members of the
sysadmin server role automatically impersonate the 'dbo' user in all
databases, even though their login is not necessarily mapped to the
'dbo' user.
--
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
--
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:NjItb.1924$1u4.548@.news-binary.blueyonder.co.uk...
> "Nico" <anonymous@.discussions.microsoft.com> wrote in message
> news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> > Just copied all databases, from Windows NT4 with SQL 7 in a domain
to a
> new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database
> wizard.
> > Now a few databases still have a dbo with login name
> DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few
> weeks from now.
> > 1. Can I get a problem when I don't resolve this?
> > 2. Is there a way to give the dbo the sa login name?
> First off, 'dbo' is a database role, not a user. A role is like a
group in
> Windows. Anyone you assign to the dbo role in a database can do
anything
> they want in that database.
> 'sysadmin' is a server role that can do anything anywhere in your SQL
server
> (and so is like a super dbo, if you like). By default, the local
admin
> group and the user sa are both assigned to the sysadmin role, and so
can
> both act as the dbo of a database (plus additional actions). So there
is no
> need to add sa to the dbo as it is already more powerful than dbo.
> Any other user that you want to have dbo access just add them to the
> appropriate database's dbo role. If your DOMAIN/Administrator is
going,
> then leaving the login there won't be a problem, as no-one will be
able to
> use it (though for completeness you might want to remove it). You can
add
> as many other accounts as you feel is safe to the dbo role.
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003
>|||You can change database ownership to 'sa' with sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
If you're not running SQL 2000 SP3, you may get an erroneous error
stating that the proposed owner is already a user in the database. In
that case, temporarily change ownership to a non-conflicting login:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'sa'
EXEC sp_droplogin 'TempOwner'
GO
--
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
--
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to
a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
>|||Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change the dbo login to sa?|||sp_change_users_login?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <nvk@.galvano.nl> wrote in message news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change
the dbo login to sa?|||Tried this before:
dbo is a forbidden value.
See also books online says sa and dbo cannot be used.
Another suggestion?|||How did you change the owner? sp_changedbowner?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:0E3B442E-0722-4D65-9E54-2DC90AE18C29@.microsoft.com...
> Tried this before:
> dbo is a forbidden value.
> See also books online says sa and dbo cannot be used.
> Another suggestion?|||Perhaps this is an Enterprise Manager refresh issue. Does sp_helpdb
'MyDatabase' return the correct owner?
--
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
--
"Nico" <nvk@.galvano.nl> wrote in message
news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the
properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is
there a possibility to change the dbo login to sa?|||Yes, this works fine when I look at the database properties
Just found out a procedure to solve the problem
1. detach database (Query Analyser
2. copy mdf and ldf files (Explorer
3. create database (Query Analyser
4. Delete database (Enterprise Manager
5. copy back mdf and ldf files (Explorer
6. attach database (Query Analyser
Now the dbo user has login name of sa (login name Query Analyser when created database).