Re: MS SQL 2K thinks user is both there and NOT there... what gives???

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 01/29/04

  • Next message: Richard Ding: "Re: MS SQL 2K thinks user is both there and NOT there... what gives???" 
    Date: Thu, 29 Jan 2004 17:11:00 -0000

    Hi Alex,

    You can fix this with the system stored procedure sp_change_users_login.
    Try:
    EXEC sp_change_users_login 'AutoFix', 'padmin' from the database. 

    -- 
Jacco Schalkwijk
SQL Server MVP
"Alex" <alex@totallynerd.com> wrote in message
news:2ba4b4eb.0401290903.34126c7c@posting.google.com...
> Hi all,
>
> I've restored a database, and MS SQL is giving me conflicting messages
> on whether my main login user is there or not.
>
> My main user - padmin - on the old DB was setup as owner, and padmin
> is listed under Logins.  But when I select my database > Users then
> select padmin and check db_owner and OK, I get this message:
> Error 15410: User or roll 'padmin' does not exist in this database.
>
> Uhh, it's there, I'm looking at it.  When I click on Permissions
> button in the same window, I get this message:
>
> Error 15001: Object '(null)' does not exist or is not a valid object
> for this operation.
>
> So... I goto Security > Logins and select padmin (the user SQL says
> doesn't exist) and manually permit it to have access and db_owner role
> of my database.  I get this error:
>
> Error 21002: [SQL-DMO] User 'padmin' already exists.
>
> When I try to delete padmin, I get this error:
>
> Error 15174: Login 'padmin' owns one or more database(s).  Change the
> owner of the following database(s) before dropping login... and it
> lists my database.
>
> So, from within my database, padmin doesn't exist and isn't the owner,
> but to MS SQL 2000 it does exist and IS the owner.
>
> What gives???  How can I get past this?  I have programs and many ODBC
> connections that use padmin, so I can't simply create another user and
> use this.  I'm guessing this is nothing more then a bug in MS SQL, but
> I have no idea how to resolve it.
>
> Thanks for any ides, solutions, or suggestions... and take care,
>
> Alex.
  • Next message: Richard Ding: "Re: MS SQL 2K thinks user is both there and NOT there... what gives???"