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?
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With