Re: SQL 2000 SP3 DB User Login Name changes

From: Tom (Tom_at_discussions.microsoft.com)
Date: 11/02/05

  • Next message: Dan Guzman: "Re: SQL 2000 SP3 DB User Login Name changes" 
    Date: Tue, 1 Nov 2005 19:25:03 -0800

    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
    >
    >
    >

  • Next message: Dan Guzman: "Re: SQL 2000 SP3 DB User Login Name changes"

    Relevant Pages

    • RE: How do I restore from mdf and ldf files?
      ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
      (microsoft.public.sharepoint.portalserver)
    • RE: ESENT Logging
      ... SBS server 2003 has a backup job that will ... 145881 How to Use Jetpack.exe to Compact a WINS or DHCP Database ... newsgroups so that they can be resolved in an efficient and timely manner. ...
      (microsoft.public.windows.server.sbs)
    • RE: ESENT Logging
      ... SBS server 2003 has a backup job that will ... 145881 How to Use Jetpack.exe to Compact a WINS or DHCP Database ... Microsoft CSS Online Newsgroup Support ...
      (microsoft.public.windows.server.sbs)
    • RE: Backups have Shadow Copy Problems
      ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
      (microsoft.public.windows.server.sbs)
    • RE: Adventures in Server restore
      ... SP1 for exchange change the db jet and should fix this problem ... > I thought I'd pass on my experience in bringing back an exchange server ... I'm not sure what happened (but a online backup was going on) ... > persists then please restore the database from a previous backup. ...
      (microsoft.public.exchange.admin)