Re: User already exists in the current database but without LOGIN

From: JD (joeydba_at_yahoo.com)
Date: 01/26/05

  • Next message: Alex: "RE: Who deleted my records?" 
    Date: Wed, 26 Jan 2005 16:47:07 -0600

    If you haven't already deleted and recreated the login, do this:

    USE MYPROD
    go
    exec sp_change_users_login 'Auto_Fix', 'MYWEBUSER'
    go

    This will fix it. You can read about sp_change_users_login in BOL.

    HTH

    "JJA" <johna@cbmiweb.com> wrote in message
    news:1106762160.654247.325760@z14g2000cwz.googlegroups.com...
    > I upgraded my SQL Server 7.0 database, MYPROD, by detaching it from
    > SERVERNT, copying all its NT files to my new SERVER2003, and then I
    > EXECUTEd sp_attach_db to make it known to my default instance of SQL
    > Server 2000 (running on Windows Server 2003).
    >
    > Thru Ent Manager, I notice that the Users entry for this database shows
    > MYWEBUSER as the USERNAME, <NONE> for the LOGIN name, and when I click
    > on the entry in the grid, the permissions are checked for PUBLIC and
    > DBO (both of which I want).
    >
    > I have a perfectly good LOGIN called MYWEBUSER already in place on
    > SERVER2003 (and also on the old SERVERNT) but somehow in the process of
    > copying the files the relationship of this USER to a LOGIN has not been
    > copied over (i.e. this user entry has nothing for LOGIN).
    >
    > So I tried this:
    >
    >
    > USE MYPROD
    > GO
    > EXEC sp_grantdbaccess @loginame = 'mywebuser'
    >
    > Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line
    > 147
    > User or role 'mdwebuser' already exists in the current database.
    >
    > I've looked around a lot and cannot seem to find how to script this
    > out. I am trying to avoid:
    > a. manual DELETE of the USER entry from MYPROD
    > b. manual visit to LOGINs, click on MYWEBUSER, and click of MYPROD
    > database
    > (step b seems to add MYWEBUSER into MYPROD with MYWEBUSER as a valid
    > LOGIN)
    >
    > I know USER entry is different from the LOGIN entry. Is my problem due
    > to the fact that they are named the same? I can fix this manually but
    > would like to build a script to be run after this COPY with UPGRADE
    > operation. Thank you in advance for help.
    >

  • Next message: Alex: "RE: Who deleted my records?"