June 20, 2003 at 10:07 am
Had a hard disk failure on our development machine. Recovered the database file and attached this on a new drive but now get the above message when I try to add a user.
I have deleted the user manually and also using the drop procedure.
How can I reinstate users?
June 20, 2003 at 10:56 am
In preparation to moving my server files to new array, I had to move one database from one server to another. When I tried to add users to the database after the move, I couldn't as they 'already existed.' Luckily I had read about Orphaned Users in this site and others.
You need to run the following command in the database you are trying to add the user:
exec sp_revokedbaccess 'login'
or if it's a domain account
exec sp_revokedbaccess 'domain\login'
Then you will be able to add the user or role back in.
-SQLBill
June 22, 2003 at 11:21 pm
Run the following while you are in ur recovered database.
sp_change_users_login 'auto_fix',user
Try this.
Shamshad Ali.
June 23, 2003 at 4:15 am
That's a better answer. Bill, if you do the revokedblogin, you have to reset the permissions. SP_Change_users_login will fix the problem more directly. Lot's of information on the site about it.
StefanJ, this will happen anytime you move an mdf to a new server if you don't do some extra work. One way is the track you're on, add the user, then run sp_change_users_login. A better way is to use sp_addlogin to add the login first with the same SID as on the original server where the mdf came from. Then when you attach everything will line up.
Andy
June 23, 2003 at 7:55 am
Many thanks for the replies.
I am a bit new to SQL Server so excuse the dumb questions.
Tried shamshad solution which did the trick. Just a case of running for each user and then resetting the password from null.
I will shortly be getting a proper server, we are currently using a standard pc as a test machine.
I noted Andy's comments and also read the faq "Fixing broken logins and transferring passwords" but am not entirely clear on the steps I should follow to transfer a database from one server to another and preserve the logins.
If I understand correctly the user ids will transfer OK when I detach and reattach the database file. But somehow I have to transfer the logins, SID, User ID, passwords from the old to the new servers.
Is there a straightforward (idiot proof), step by step instruction for doing this?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply