Users lost when restoring database

[apollo11]

When I restore my SQL 7.0 database backup I do not see the users that I originally had in the database. What's strange is , if I create those users afresh and try to add them Enterprise manager throws an error saying that the user already exists. Is there a way to remove a user from any of the systables? so that I can add them again?

 

[tlbroadbent]

I've never seen users fail to restore properly unless the backup come from a differenct server or the master database was recreated since the backup was made. You can usually use sp_change_users_login to synch database users and server logins. See SQL BOL or the following online link.

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp

If this doesn't help, you can set ad hoc system table updates On and delete rows from the sysusers table in the database. Use this option very carefully. You may make the DB inaccessible. Be sure to set the ad hoc update option off when finished. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Bernadette]

Hi There

To Delete the Users and the Logins from the Server
Try the following script


-- ALLOW MODIFICATIONS TO THE SYSTEM DATABASES
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO


-- DELETE THE LOGINS FROM THE MASTER DATABASE
Use Master
delete from syslogins
where name = '<Login Name>'
GO


--DELETE THE USERS FROM THE USER DATABASE
Use <USER DATABASE NAME>
GO
Delete from sysusers
where name = '<User Name>'
GO


-- DISALLOW MODIFICATIONS TO THE SYSTEM DATABASES
sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE


Hope This Helps Bernadette