SQL Server - New Logins / Users don't work

Posted on 2012-08-13
Last Modified: 2012-08-15
I restored an existing database. Non of the Logins or Users work on the new server.
I tried create a new LOGIN and USER. I can't even connect using them in Management Studio.
Any ideas?
Question by:JElster
    LVL 5

    Assisted Solution

    If you're moving a database from one server to another, you need to also port the user accounts. The only thing stored with the database is which users have access to what. The location where the users are defined is somewhere else and would not be ported over by just restoring the database to a new server.

    You're going to need the sp_help_revlogin script. See this link:
    LVL 1

    Author Comment

    It didn't work.. the login are already there and I can't delete the existing ones. They are references schema's I can't even find
    How can I create a new on that will work?
    LVL 1

    Author Comment

    I tried this

       WITH PASSWORD = 'test123'

    then I get login fails
    LVL 25

    Assisted Solution

    Does the user in the database have the logins existing in the new database? If yes, you need to take care of the orphan users. Run the following script

     DECLARE @username varchar(25)
          DECLARE fixusers CURSOR

          SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
          and suser_sname(sid) is null and [name] in (select [name] from master.dbo.syslogins)
          ORDER BY name

          OPEN fixusers

          FETCH NEXT FROM fixusers
          INTO @username

          WHILE @@FETCH_STATUS = 0
          EXEC sp_change_users_login 'update_one', @username, @username
          FETCH NEXT FROM fixusers
          INTO @username

          CLOSE fixusers
          DEALLOCATE fixusers
    LVL 2

    Accepted Solution

    Please check the mentioned details in attached screen by login using Windows Authentication.

    Select your User name and then right click for properties.

    In these properties, Set Server Role and User Mapping.

    Hope this will resolve your problem.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now