Re: SQL 2000 SP3 DB User Login Name changes

• Previous message: Tom: "Re: SQL 2000 SP3 DB User Login Name changes"
• In reply to: Tom: "Re: SQL 2000 SP3 DB User Login Name changes"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Tue, 1 Nov 2005 22:01:58 -0600

> I remember trying the sp_changedbowner today but it gave me an error
> message
> or didn't work, I can't remember which.

Not much to go on but perhaps you got an error like 'the proposed owner is
already a user in the database'. The owner cannot already be an existing
database user so try dropping the user with sp_dropuser and then try
sp_changedbowner again. In some cases, you'll get that error because of a
mismatch. You can work around the issue by temporarily changing the owner
to a non-conflicting login:

USE MyDatabase
EXEC sp_addlogin 'tempowner'
EXEC sp_changedbowner 'tempowner'
EXEC sp_changedbowner 'NewOwner'
EXEC sp_droplogin 'tempowner'

I doubt your problem is related to the OS differences.

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom" <Tom@discussions.microsoft.com> wrote in message 
news:1B9D409C-AA0D-49D5-B7A6-6BC359926D39@microsoft.com...
> Dan,
> I was logged into our Win2K3 SP1 server using the Enterprise Manager at 
> the
> office for both situations, detach and backup.  Both situations were
> performed within minutes of each other (2-5 at most).
> I've done other detach/attaches and I've not had any issues when doing so 
> on
> other servers.  Off the top of my head the only difference was Win2K and
> Win2K not Win2k3 and Win2k3 but we're dealing with SQL 2000 here and it
> shouldn't matter,s hould it?
> I remember trying the sp_changedbowner today but it gave me an error 
> message
> or didn't work, I can't remember which.  THis was used in the SQL 
> Analyzer.
> Thanks for your responses!!
> Tom
>
> "Dan Guzman" wrote:
>
>> The cause of these symptoms are mismatches between syslogins and sysusers
>> and invalid database owners.  This is common when you attach or restore a
>> database to a different server.
>>
>> It looks like your database owner is invalid.  You can correct this with
>> sp_changedbowner:
>>
>>     USE MyDatabase
>>     EXEC sp_changedbowner 'SomeLogin'
>>
>> You can identify and correct mismatches between syslogins and sysusers 
>> using
>> sp_change_users_login.  See the Books Online for usage details.
>>
>> I'm not sure why you have different behavior with attach vs. restore. 
>> Were
>> you logged in using the same account for both operations?
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Tom" <Tom@discussions.microsoft.com> wrote in message
>> news:691F02CF-16EE-422C-B534-9154FC9A1FD6@microsoft.com...
>> > Server: Win2K3 SP1    [source and destination]
>> > SQL:     SQL 2000 SP3 [source and destination]
>> >
>> > SOURCE: I have a SQL 2000 SP3 database ("ABC") at the office that has 
>> > the
>> > following (correct) properties under Users.  [ABC was created under SQL
>> > 2000
>> > SP3 a couple of weeks ago and I've just imported data into ABC since
>> > creating
>> > it.]
>> > Name      Login Name       Database Access
>> > dbo                                 Permit
>> > sysdba    sysdba               Permit
>> >
>>
>> > I created BOTH a backup AND detached the ABC database.
>> >
>> > DESTINATION: When I get to the client's site to install DB "ABC", every
>> > time
>> > I attached the ABC database, I received these properties:
>> > Name     Login Name        Database Access
>> > dbo        sysdba               Permit
>> > sysdba                            Permit
>> >
>> > [Notice that the dbo user incorrectly has 'sysdba' as a Login Name 
>> > after
>> > the
>> > attach and the sysdba user doesn't have any Login Name.  The correct 
>> > setup
>> > is
>> > user dbo has no Login Name and user sysdba has a Login Name of 
>> > 'sysdba'.]
>> >
>> > I could not see how this was 1) happening nor 2) could I determine how 
>> > to
>> > fix/correct it.
>> >
>> > When I used the restore of the ABC database, my DB Users showed 
>> > correctly:
>> > Name     Login Name      Database Access
>> > dbo                              Permit
>> > sysdba    sysdba            Permit
>> >
>> > 1) Why did the 'switch' of Login Names occur during the Attach process 
>> > and
>> > not the restore process from a previously made backup?  The restore was
>> > from
>> > a backup made this morning and the attach was from a detach made this
>> > morning
>> > on the same server in the same domain all under the same SQL 2000 SP3.]
>> > 2) How can I fix/correct the situation?  I just 'happened' to have a
>> > backup
>> > since previously I've always used detach/attach.  That is, how can I
>> > change
>> > User dbo with a 'sysdba' Login Name have a blank Login Name AND to 
>> > change
>> > User sysdba with no Login Name to have a Login Name of 'sysdba'?
>> >
>> > TIA,
>> > Tom
>>
>>
>> 

• Previous message: Tom: "Re: SQL 2000 SP3 DB User Login Name changes"
• In reply to: Tom: "Re: SQL 2000 SP3 DB User Login Name changes"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]