Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

restore database user login error

Tags:

sql-server

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?

like image 361
user441365 Avatar asked Oct 18 '10 11:10

user441365


People also ask

How do I associate a user to login in SQL Server?

Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions.


2 Answers

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.

like image 167
codingbadger Avatar answered Sep 19 '22 05:09

codingbadger


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
like image 30
Andomar Avatar answered Sep 21 '22 05:09

Andomar