Unable to create users in a database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Unable to create users in a database

Author

Topic

Eve
Starting Member

4 Posts

Posted - 2002-10-09 : 11:27:14

I am running sql 7 on windows nt4 platform. We are gradually moving our database from windows nt4 server to a better spec windows 2000 server. When we restored the master database to a new server it failed. So I moved only the databases and I have created the login for all users but I am not able to add users to the database as the users where not restored when the database was moved, when I try to add a user to the database an error message pops up saying the user is already created, but if you view the user icon in the database no user is listed.

Please help.

Thanks

Eve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2002-10-09 : 17:14:20

You should never restore the master database onto any server except where it was backed up.

You will need to run sp_change_users_login to synchronize the user information with the login information.

Here is a script that can be run in each of the user databases:

SET QUOTED_IDENTIFIER OFF
GO

DECLARE @SQL varchar(100)

DECLARE curSQL CURSOR FOR
select "exec sp_change_users_login 'AUTO_FIX','" + name + "'"
from sysusers
where issqluser = 1

OPEN curSQL

FETCH curSQL into @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL)
FETCH curSQL into @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

go

scottpt
Posting Yak Master

186 Posts

Posted - 2002-10-10 : 16:13:24

Re-install on the WIN2K server.
To move logins
move these columns from the NT sqlserver to the 200 server
SELECT [xstatus], [xdate1], [xdate2], [name], [password], [dbid], [language]
FROM [master].[dbo].[sysxlogins]
where name <> 'sa'
and name not like 'repl_%'
and name not like '%/%'

EX.
sp_configure 'allow updates',1
go
reconfigure with override
go
INSERT INTO sysxlogins ([xstatus], [xdate1], [xdate2], [name], [password], [dbid], [language])
VALUES .....
GO
sp_configure 'allow updates',0
go
reconfigure with override
go
When you restore or attach your db the spids will alreay match and the pwd will not change.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources