Post reply

Copy Database and weird user problem

  • January 4, 2006 at 5:10 pm

    #167567

    Hi all,

    Just successfully copied my database from 2000 to 2005 and was just checking the users/security and when I open up a user and then click OK to close it, the following message is raised:

    Login Name must be specified. (SqlManagerUI)

    These are specifically sql logons which are having this issue.

    I did have this problem before (but since then I have started again) and basically I needed to remove the reference to this user in all of the areas (schema's, security, logons, etc) and then delete the user and recreate it and all the associations.

    Any ideas on why this might be happening?

    Thanks

    Troy

  • January 5, 2006 at 3:41 am

    #612821

    Run this to find orphaned logins:

    USE ;

    GO;

    sp_change_users_login @Action='Report';

    GO;

  • January 5, 2006 at 12:42 pm

    #613020

    Okay, so the login has been orphaned...but why? When I copied the database across the usernames were selected and they imported fine (no errors). I can open the user in the security area of the Mgmt Console (no errors), only when I go to use or open and close the user against the database is when this error comes up.....

    I have got around the problem, by removing the references in the schema's and then removing and reapplying the user to the database - and it is all working fine - but just finding this a little strange.

     

  • January 6, 2006 at 1:49 pm

    #613388

    Troy,

    it is By Design. Users are stored in Sysusers. Logins are stored in Syslogins. Both have SID (Security ID) that have to be the same. If you move the database then the login should be created on the second server with the same SID. By default it is created with random SID, that is why you have this error.

    Do the following (change MyDatabase for your database name) and compare all 4 result sets for SID and Name and you will understand what is going on.

    In SQL Server 2000:

    Use MyDatabase

    select * from sysusers

    select * from master.dbo.syslogins

    In SQL Server 2005:

    Use MyDatabase

    select * from sysusers

    select * from master.sys.syslogins

    Regards,Yelena Varsha

  • January 8, 2006 at 12:47 pm

    #613442

    Ahhhh brilliant thanks Yelena for the clarification!

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

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