dbowner no more dbower (SID lost)

hello eveybody,
I have an Sql server 2000 installed with several databases.
this instance is configured with both sql server identification and
Windows ID
I have created a login called john and this login has a default
database mydb (sp_addlogin)
On this default database, i have granted the Access to this login
(sp_grantdbaccess)
a new application has created a new database newdb and, for
differents reasons, the program has created again the john login
(same name)
Now, I can Access with this login to the database newdb but no more
to the default one mydb .
When I say i can Access... that means :
Select * from toto is working on newdb
(I dont need to write Select * from john.toto)
But with mydb ,
Select * from toto does not work,
I need to write the select this way : Select * from john.toto
Since I use an application, I cannot change the sql statement
since john is "dbowner" and has a defaut db "mydb", this is strange
When I read master.dbo.sysusers, the user john has a complexe SID
( 0x93F921DB3... )
When i read master.dbo.sysdatabases :
The new database newdb has the same complexe SID ( 0x93F921DB3...
)
The old database mydb has lost the SID witch is replaced with 0x01
The table toto has the correct UID that is john in sysusers table
How can I reset "john" owner on all tables from mydb
many thanks
nicolasIt looks like someone has changed the owner of the old database to be sa
(sid = 0x01). You can execute sp_changedbowner to reset the database owner
to be John.
See
http://msdn.microsoft.com/library/d...>
cz_30s2.asp,
for more information on sp_changedbowner.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns980E2BCBAB870140507NA02619@.207.46.248.16...
> hello eveybody,
> I have an Sql server 2000 installed with several databases.
> this instance is configured with both sql server identification and
> Windows ID
> I have created a login called john and this login has a default
> database mydb (sp_addlogin)
> On this default database, i have granted the Access to this login
> (sp_grantdbaccess)
>
> a new application has created a new database newdb and, for
> differents reasons, the program has created again the john login
> (same name)
> Now, I can Access with this login to the database newdb but no more
> to the default one mydb .
> When I say i can Access... that means :
> Select * from toto is working on newdb
> (I dont need to write Select * from john.toto)
> But with mydb ,
> Select * from toto does not work,
> I need to write the select this way : Select * from john.toto
> Since I use an application, I cannot change the sql statement
> since john is "dbowner" and has a defaut db "mydb", this is strange
> When I read master.dbo.sysusers, the user john has a complexe SID
> ( 0x93F921DB3... )
> When i read master.dbo.sysdatabases :
> The new database newdb has the same complexe SID ( 0x93F921DB3...
> )
> The old database mydb has lost the SID witch is replaced with 0x01
>
> The table toto has the correct UID that is john in sysusers table
> How can I reset "john" owner on all tables from mydb
> many thanks
> nicolas
>|||many thanks for your help
I try the command but sql server say :
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database
I'm connected with the sa account into the default database of the owner
"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:

> It looks like someone has changed the owner of the old database to be
> sa (sid = 0x01). You can execute sp_changedbowner to reset the
> database owner to be John.
> See
> http://msdn.microsoft.com/library/d...ry/en-us/tsqlre
> f/ts_sp_ca-cz_30s2.asp, for more information on sp_changedbowner.
> Thanks
>|||Try creating a temporary login, change the database to be
owned by that temp login. Then change the database to be
owned by John and drop the temp login. Something like:
use YourDatabase
go
exec sp_addlogin 'TempLogin'
exec sp_changedbowner 'TempLogin'
exec sp_changedbowner 'John'
exec sp_droplogin 'TempLogin'
-Sue
On Thu, 27 Jul 2006 18:00:28 -0700, 906507N
<906507N@.10090.I20034> wrote:
[vbcol=seagreen]
>many thanks for your help
>I try the command but sql server say :
>Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database
>
>I'm connected with the sa account into the default database of the owner
>
>"Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
>news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:
>|||If this error comes as a result of attempting to make the owner to be
"John", then it means that while the owner was sa, a user was already
created for John in the database. If you now want to make John the database
owner, you should remove that user:
sp_dropuser 'John'
and then you should be able to call
sp_changedbowner 'John'
There is a catch: if 'John' owns objects in the database, you cannot drop
him, so you will have to change the ownership of those objects to someone
else first, by using sp_changeobjectowner.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns980E1E98F3B4D0140507NA02619@.207.46.248.16...
> many thanks for your help
> I try the command but sql server say :
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database
>
> I'm connected with the sa account into the default database of the owner
>
> "Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
> news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:
>
>|||906507N <906507N@.10090.I20034> wrote in
news:Xns980E1E98F3B4D0140507NA02619@.207.46.248.16:

> many thanks for your help
> I try the command but sql server say :
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line
47
> The proposed new database owner is already a user in the database
>
> I'm connected with the sa account into the default database of the
owner
>
> "Laurentiu Cristofor [MSFT]" <laur@.nospam.com> wrote in
> news:ewzvm2dsGHA.3496@.TK2MSFTNGP06.phx.gbl:
>
us/tsqlre[vbcol=seagreen]
>
sue and Laurentiu,
many thanks for your help
The solution was here
I created a new login, changeobjectowner for each database tables from
old user,delete and create again the old login, changeobjectowner back to
the old user all objects
now, it is correct
again, many thanks for your answers.
nicolas