HELP! Moving user and databases between servers:
-
I am moving a handful of Databases from a SQL Server 7.0 Instance to a Server running SQL Server 2000. I have done this in the past with no problem. until now. I'm not sure what I am doing wrong.
One database has 1 SQL User. I created the user on the destination server using a script that recreates the user with the same password and SID:
DECLARE @pwd sysname
-- Login: auser
SET @pwd = CONVERT (varbinary(256), 0759313C4D5CxxxxxxxxxxA26)
EXEC master..sp_addlogin 'auser', @pwd, @sid = 0x93xxxxxxxxxxx71199D20255, @encryptopt = 'skip_encryption'
Then I created my database on the destination server and restored it from a recent backup of the source server.
In the past, this procedure has also restored the permissions for the newly created user as well. This time, it did not recreate the permissions for the user. When I try to add the user manually, (add a database user from EM) the user does not show up in the users list.
When I go to the security tab, the user shows up, but when I try and change the permissions manually there, I get the following error: error 21776 - the name was not found in the user collection. When I try to delete this user, I get the same error.
I 'think' if I drop my new database it will then let me drop the user (????) then I should recreate the new database FIRST and then create the user with my script THEN do my restore. (????)
I'm asking because I was wondering if anyone had run into this before and I don't want to create a bigger problem than I already have.
any help would be appreciated.
-
Have you tried...?
EXEC sp_change_users_login 'Auto_fix', 'userlogin'
-
would I run that for the user against the master database or the newly created user database? I guess I'm confused as to why this happened at all.. I have alot of databases to move!!!
-
Very good article and thank you all for the help!!!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply