restore database user login error

Question

I'm trying to restore a database (SQL SERVER 2008) from a backup in a different server. The problem I have is with the login, as the user is included in the backup but the login is not.

So I try to create a new login in the server but it doesnt seem to work.

anyone knows a workaround for this?

Answer 1

It would have been useful if you posted the actual error message and the steps taken to produce the error.

Anyway, I think what you need to do is delete the user from the restored database. Then you will be able set up the user & corresponding Server login from scratch.

EDIT:

If the user owns a schema in the database you won't be able to delete the user. There is Microsoft article on how to transfer SQL Logins.

Answer 2

That's a very common problem after a restore. A user (database specific) and a login (server wide) both have a SID. The problem is probably that the login you created has a different SID from the login on the production database. You can check the login and user SID like:

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

Here's a script we run after every backup to repair the login - database 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