• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

SQL Server - New Logins / Users don't work

Hi..
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?
0
JElster
Asked:
JElster
3 Solutions
 
mwheeler1982Commented:
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: http://blog.netnerds.net/2009/01/migratetransfer-sql-server-2008200520007-logins-to-sql-server-2008/
0
 
JElsterAuthor Commented:
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?
thx
0
 
JElsterAuthor Commented:
I tried this


CREATE LOGIN test
   WITH PASSWORD = 'test123'
   
GO


then I get login fails
0
 
TempDBACommented:
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
      FOR

      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
      BEGIN
      EXEC sp_change_users_login 'update_one', @username, @username
      FETCH NEXT FROM fixusers
      INTO @username
      END

      CLOSE fixusers
      DEALLOCATE fixusers
GO
0
 
rajshekherCommented:
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.
Answer.png
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now