'dbo' mapped to non-existent login - what to do? (SQL 2000)

Have you used sp_changedbowner before Andrew? provided the id is not a user
in the database, this will changed the dbo.
Chris Wood
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server
> in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo
> in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>You're feeling is right, don't update the system tables. Use sp_changedbowne
r instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server
in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo
in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||Gentlemen:
Thank you very much for your help.
The funny thing is that the owner of the database is set correctly (e.g. in
the Enterprise Manager --> database properties --> 'General' tab),
only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
Therefore I haven't use sp_changedbowner, but I will give it a try next
time.
Thanks a lot again!
Best regards,
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> You're feeling is right, don't update the system tables. Use
> sp_changedbowner instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This hopefully explains it:
The owner of a data is stored in two places. It is stored in a system table
in the master database
(sysdatabases), but it is also reflected in sysusers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:%23peUt66kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Gentlemen:
> Thank you very much for your help.
> The funny thing is that the owner of the database is set correctly (e.g. i
n
> the Enterprise Manager --> database properties --> 'General' tab),
> only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
> Therefore I haven't use sp_changedbowner, but I will give it a try next
> time.
> Thanks a lot again!
> Best regards,
> Andrew
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>
>|||Hello,
If I restore a database backup from a foreign system (separate SQL Server in
a separate domain) then the result of:
sp_helpuser @.name_in_db = 'dbo'
is:
UserName GroupName LoginName SID
-- -- --
dbo db_owner NULL <SID>
Hence, 'dbo' is mapped to NULL.
It causes problems if Cross DB Ownership Chaining is enabled, because dbo in
different databases is mapped to different logins.
What is the right way to correct this problem?
So far, I have performed an ad-hoc update to system table sysusers setting
the sid for 'dbo' user to the correct value:
update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
master..syslogins table)
But I've got a feeling this is not the right way...
What should I do?
Thank you for your help!
Best regards,
Andrew|||Have you used sp_changedbowner before Andrew? provided the id is not a user
in the database, this will changed the dbo.
Chris Wood
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server
> in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo
> in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||You're feeling is right, don't update the system tables. Use sp_changedbowne
r instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server
in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo
in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||Gentlemen:
Thank you very much for your help.
The funny thing is that the owner of the database is set correctly (e.g. in
the Enterprise Manager --> database properties --> 'General' tab),
only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
Therefore I haven't use sp_changedbowner, but I will give it a try next
time.
Thanks a lot again!
Best regards,
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> You're feeling is right, don't update the system tables. Use
> sp_changedbowner instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This hopefully explains it:
The owner of a data is stored in two places. It is stored in a system table
in the master database
(sysdatabases), but it is also reflected in sysusers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:%23peUt66kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Gentlemen:
> Thank you very much for your help.
> The funny thing is that the owner of the database is set correctly (e.g. i
n
> the Enterprise Manager --> database properties --> 'General' tab),
> only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
> Therefore I haven't use sp_changedbowner, but I will give it a try next
> time.
> Thanks a lot again!
> Best regards,
> Andrew
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>
>