Post reply

Fix orphaned users created WITHOUT LOGIN.

  • September 10, 2012 at 5:28 am

    #280985

    So I have restored a database to a new server and a check reveals a DB-user being orphaned. However, this user must have been created as CREATE USER WITHOUT LOGIN, for on the original server this user is not connected with a login.

    Now, how do I solve the orphaned user? Is there a problem, I wonder, since there was no login in the first place? sp_change_users_login does not work here, for it needs a login to connect the db-user with.

    Any help?

    Greetz,
    Hans Brouwer

  • September 10, 2012 at 5:42 am

    #1535629

    FreeHansje (9/10/2012)

    So I have restored a database to a new server and a check reveals a DB-user being orphaned. However, this user must have been created as CREATE USER WITHOUT LOGIN, for on the original server this user is not connected with a login.

    Now, how do I solve the orphaned user? Is there a problem, I wonder, since there was no login in the first place? sp_change_users_login does not work here, for it needs a login to connect the db-user with.

    Any help?

    there's nothing to fix;

    if you create a user without login, it literally never logs in...so there is nothing to change.

    you might create a user like that so that you can run EXECUTE AS under higher permissions in a procedure or trigger, but noone will ever use that USER to actually connect.

    If you need an example, let me know, but you should be good to go if you fixed the other users that do have logins.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 10, 2012 at 7:33 am

    #1535678

    Tnx, I figured that much, but it is good to read confirmatiuon.

    Greetz,
    Hans Brouwer

  • September 11, 2012 at 3:14 am

    #1536071

    The user may have been created as a login as normal and given database permissions but then if the server login was deleted the user would be left in the database I believe.

    If users were sql logins in the database and valid on the old server you need to create the sql logins on the new server and just default them to master database then run sp_change_users_login to link the server login to the database login

Viewing 4 posts - 1 through 3 (of 3 total)

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