Re: SQL 2000 SP3 DB User Login Name changes

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/02/05 

Date: Tue, 1 Nov 2005 20:47:01 -0600

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

Relevant Pages

  • Re: Script A Login
    ... Yes, I do use Windows Authentication, but I am being asked to convert to SQL ... When I restore a database, ... database User Accounts and Database Roles are restored. ... Login in the server and the User Account in the database is broken? ...
    (microsoft.public.sqlserver.security)
  • Re: Script A Login
    ... exacutes a number of stored procedures against the database, ... Login and User ... I restore databases all the time and don't need to re-create logins or ... The user account should have been in the database ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 SP3 DB User Login Name changes
    ... already a user in the database'. ... >> I'm not sure why you have different behavior with attach vs. restore. ... Login Name ... >>> attach and the sysdba user doesn't have any Login Name. ...
    (microsoft.public.sqlserver.security)
  • Re: "Best Practices" way to distribute MSDE
    ... Ways to put a database into a server: ... Restore a users's selected backup. ... if i'm supposed to be using MSDE now instead of Jet: ... > if you can't connect with that login, ...
    (microsoft.public.sqlserver.msde)
  • attaching content db to managed path
    ... database move it to another server create a new web app and attach it. ... I would like to be able to restore a content database to a path. ...
    (microsoft.public.sharepoint.portalserver)