cannot delete user login

  • jessica-321853

    SSCrazy

    Points: 2207

    I have a problem, an user tried to enter an application and it displays an error message, saying his user doesnt have permissions to enter that DB. And the user exists as Login in SQL Server and as user in the DB, I try to check the permissions in the DB and it displays the next message "Error 15001: Object '(null)' does not exist or is not a valid object for this operation", then I tried to assign to it a role, but it displays the error 15410: user or role does not exist in this database. Then if I want to delete it it displays error 15008: User does not exist in the current database. And if I want to assign permissions to the DB from Security->logins it shows the error 21002:[SQL-DMO] User already exists.

    Can anybody help me??

  • Nilssond

    Hall of Fame

    Points: 3285

    Check to see what name is actually used in the database. Often, DOMAN\username is set as the SQL Server Login, but the database user name may just be the username of the DOMAIN\username combination

  • SQLGuy64

    Ten Centuries

    Points: 1051

    first, get specifics on login

    EXEC xp_logininfo  'DOMAIN/WindowsGroupName', 'all'

    then

    EXEC sp_revokelogin  'login'

  • ShekharNaidu

    SSCrazy

    Points: 2092

    hi

      did you try out this one

    sp_dropuser 'username'

    after executing this try to add the user again..

     

    Thank You

    Shekhar


    Thanks ,

    Shekhar

  • Cath Trimble

    SSCarpal Tunnel

    Points: 4201

    It could be that the user has been orphaned - happens when the database is moved to a different server.  Try this command:

    Use mydb go sp_change_users_login 'report' 

    then this on the login returned:

    Use mydb

    go

    sp_change_users_login 'update_one', 'username', 'username'

    Cath

     

  • Frances L

    SSCarpal Tunnel

    Points: 4727

    she said an user tried to enter an application and it displays an error message. i do not know whether she need to set up the application account to login.

  • Scott Coleman

    One Orange Chip

    Points: 27416

    This sounds like an orphaned user.  If the database was restored from another server with the same SQL login, the user is in the database sysusers table with an SID value from the original server, which doesn't match the SID value for the login in the master..sysxlogins table where the database now resides.  In other words, it looks like the same name to you but it is completely different ('user does not exist') to SQL Server.  Until you try to add the user, where it refuses because of the original entry ('user already exists').  You can verify this with this command:

    USE <db>

    EXEC sp_change_users_login 'Report'

    You can fix it with this:

    USE <db>

    EXEC sp_change_users_login 'Update_One', '<user>', '<login>'

  • tim-142151

    SSC Veteran

    Points: 219

    Just to add an additional note..

    I got this error when restoring a database and the old user was still in the users list (SQL Server 2000).

    I tried to drop, etc. the user, but it said that the user didn't exist!

    I tried to refresh the database content. Still errored.

    Eventually, I closed SQL Enterprise Manager and then reopened it.

    Solved! Could delete and add a new user.

    Ridiculous!

  • dan 98255

    SSC Journeyman

    Points: 77

    Tim - I created an account just to say thanks! I spent 45 mins wrestling with this error until I read your post and tried a good old fashioned close and re-open!

    Ridiculous indeed!!

  • robertmglynn

    SSC Journeyman

    Points: 85

    SSCrazy, created an account just to say thank you for this. Saved me.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply