Repair database login error

Question

I am trying to restore a database (SQL SERVER 2008) from a backup on another server. I have a problem with the login, because the user is included in the backup, but the login is not.

So I am trying to create a new login on the server, but it does not seem to work.

Does anyone know a workaround for this?

Answer 1

It would be helpful if you would send the actual error message and the steps taken to receive the error.

In any case, I think you need to remove the user from the restored database. Then you can configure the user and the corresponding server login from scratch.

EDIT:

If the user owns the schema in the database, you cannot delete the user. There is a Microsoft article on how to transfer SQL logins.

Answer 2

This is a very common problem after recovery. The user (database-specific) and login (on the server) both have a SID. Probably the problem is that the login you created has a different security identifier from entering the production database. You can check the login and user id as:

select UserSid from sysusers where name = 'UserName' select LoginSid from master.dbo.syslogins where name = 'UserName' 

Here we run the script after each backup to restore the login link:

 declare user_cursor cursor forward_only read_only for SELECT distinct u.name FROM sysusers u JOIN master.dbo.syslogins l ON u.name = l.name WHERE u.issqluser <> 0 declare @user sysname; open user_cursor fetch next from user_cursor into @user; while @@fetch_status = 0 begin if @user <> 'dbo' begin print '' print 'Updating user "' + @user + '"' exec sp_change_users_login 'Auto_Fix', @user end fetch next from user_cursor into @user end; close user_cursor deallocate user_cursor 

Answer 3

Im not 100% sure about the cause of your error, but this script helps me when restoring the database from another server, assuming that the login and user already exist on the target server:

 EXEC sp_change_users_login UPDATE_ONE, '[username]', '[loginname]' 

(Substitute [username] with the database username and [loginname] with the server name)

Answer 4

I could not comment on the message above - but it worked, the only thing I had to do was LINE 4, where I set the default value in the field.

 JOIN master.dbo.syslogins l ON u.name COLLATE DATABASE_DEFAULT = l.name COLLATE DATABASE_DEFAULT