database user problem when restoring

Hi everybody

i am taking the full database backup on server sql1 and restoring on sql2. there are some logins in sql1 which are not in sql2. i restored the database ,and try to create the login with same name as in sql1 and try to create the user in the database ,its giving me the error the user is already in the database . when i checked the users the user is not in the database ,but he is in the public role .i try to remove from the role its not doing it..

some body please tell me fix for this broblem

ThanKs prasad

I'm in a hurry, so I can't give you a comprehensive answer, but the problem is that the db you restored still has users in the dbs SysUsers table -- even though you created new logins with the same names, SQL Server needs to have the SID from Master..SysLogins to match the SID in YourDB..SysUsers. You can take a quick look at the SIDs for your users in Master..SysLogins and compare them to YourDB..SysUsers and see what I mean. There are many ways to fix this, the best one being dependent on how many users there are and how often you do this restore. You might want to do an UPDATE to YourDB..SysLogins. Or you might want to take advantage of the @SID parameter of sp_AddLogin if you're going to be doing this restore frequently -- that way you can synch up the SIDs in the SysLogins on both servers. But considering I don't know these details nor how comfortable you are with updating system tables, one way to do this is to:

SELECT * FROM YourDB..SysUsers

then VERY CAREFULLY

DELETE YourDB..SysUsers WHERE Name
IN ('FirstUserName' , 'SecondUserName' , 'nthUserName')
Oh, yes, to delete from system tables you'll have to run

sp_Configure 'allow updates' , TRUE
RECONFIGURE WITH OVERRIDE

before you whack the users, then run this again using FALSE afterward.

Then, as long as the users exist as server logins, you'll find you'll be able to add the users to the db as you normally would. If you use Enterprise Manager to add users, don't forget to refresh when necessary.


------------
prasad at 3/14/01 5:49:35 PM

Hi everybody

i am taking the full database backup on server sql1 and restoring on sql2. there are some logins in sql1 which are not in sql2. i restored the database ,and try to create the login with same name as in sql1 and try to create the user in the database ,its giving me the error the user is already in the database . when i checked the users the user is not in the database ,but he is in the public role .i try to remove from the role its not doing it..

some body please tell me fix for this broblem

ThanKs prasad

Hi

I have this happen all the time and I have written a stored procedure that will call SP_DROPUSER then SP_ADDUSER and SP_ADDROLEMEMBER to rebuild the correct users<->login connections.


Dale