User already exists - SQL Server Forums

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)

User already exists

Author

Topic

apkaumesh
Starting Member

4 Posts

Posted - 2008-04-04 : 06:43:05

Hi
I am transfering my database "WEB_DB" to another SQL server.
I got a Login "TEST" at both the places.
I took the back up of "WEB_DB" from first server
Then i created a database "WEB_DB" to another server
Then i gave database access of "WEB_DB" to "TEST" log in (Through Enterprice manager-->Security-->LogIN-->RightClick log in and in Properties)

Then i restored the database. But now it is not showing the database access to "TEST" log in

Also i did the sp_change_users_login AUTO_FIX, 'TEST' to fix the users UID's.
Now when i am trying to give access to "TEST" for "WEB_DB" its saying User already exist.

Please advice.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-04 : 09:20:02

When you restore database,it will have same configuration what it had. Did you use sp_users_change_login 'Update_one'.This should work if you have server login already setup.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-05 : 00:04:48

Double check with sp_helpuser, ensure those db users don't map to null sql login. Once they mapped to crooect sql login, don't need grant access again.

apkaumesh
Starting Member

4 Posts

Posted - 2008-04-07 : 04:57:41

I tried doing options sp_users_change_login 'Update_one'
And also feel taht once the user and log in is mapped, no need to grant the access again.
But after doing all this, when i check properties of Login "TEST". in database access it does not show the "WEB_DB" checked
And also my stored procedured which are owned by "TEST" are not getting executed, saying "can not find the stored procedure"

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-04-07 : 10:16:02

quote:
Originally posted by apkaumesh

I tried doing options sp_users_change_login 'Update_one'
And also feel taht once the user and log in is mapped, no need to grant the access again.
But after doing all this, when i check properties of Login "TEST". in database access it does not show the "WEB_DB" checked
And also my stored procedured which are owned by "TEST" are not getting executed, saying "can not find the stored procedure"

sp_users_change_login doesn't exist. did you get an error when you ran that?

the proc you need to run is sp_change_users_login. see the docs on how to use it here http://technet.microsoft.com/en-us/library/ms174378.aspx

-ec

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-07 : 22:14:18

My bad eyechart!!!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-07 : 23:04:40

apkaumesh, did you double check with sp_helpuser? Which sql login does the user map to?

apkaumesh
Starting Member

4 Posts

Posted - 2008-04-08 : 03:34:38

rmio, Yes I did check sp_helpuser and it shows correct mapping but when I check in security->user login -> database acess , it does not assign the database and if we try to assign it says already assigned.
eyechart , It was a typo error and I have executed the correct SP- sp_change_users_login.

I tried the process(sp_change_users_login AUTO_FIX, 'TEST') on 2 machines and it worked fine on one machine with following configuration

Machine 1

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

It did not work on the following machine ie machine 2

Machine 2
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Can you help on this ? Is it a problem with different builds/versions? But Service pack is same

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-08 : 22:46:57

Can you post mapping of it here? Does that sql login map to dbo? Did you refresh dispaly in em?

apkaumesh
Starting Member

4 Posts

Posted - 2008-04-09 : 01:47:29

Thanks for your help.
The solution was working but the effect could be seen only after I relogged into Enterprise manager(came out and started again) as it was not applicable after applying refresh.
So it is working on both the machines.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-09 : 12:20:24

I ran into this exact issue two weeks ago (SQL2K sp4). EM doesn't refresh (even when using F5)!. You actually have to exit and restart EM for the changes to appear. Took me an hour to realize what I had done actually worked (something I had done a hundred times before).

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources