After a reload of Sql Server we've load the userid's in the master database. When we try to restore and/or reload we get an error that the users already exist. When we try to connect we get an error that access is denied.

Any ideas how to syncronize the master & db userids?

-- Anonymous, September 22, 2000

Answers

Cheryl,

Check the documentation for sp_change_users_login. It sounds like that stored procedure may be your answer.

Good Luck,

Eric

-- Anonymous, September 22, 2000

Thanks! I thought there was a simple answer. We ended up manually deleteing from sysusers & sysxusers, then re-adding the users. Luckily we are early in our ceridian implemenation and only had a few set up. Thanks again! Cheryl

-- Anonymous, September 23, 2000

I have seen examples where the sysusers table on one db is exported and reloaded on another db. You have to have sa rights and set the flag to allow changes to the system tables. (The example was on sybase which may not work on SQL Server)

One problem you can run into in rebuilding the users is that other databases on the server can still have references to the numerical user ids. This can result in a user not being defined in the databases because the id in master is different from the one in the user database. Just drop an add the user to the user database to re- sync the numbers. The reload process above (which scares me a bit) avoids the problem and also preserves passwords.

-- Anonymous, January 11, 2001