| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-18 : 09:44:16
|
| Al Jauregui writes "I exported a table from SQL 7 to another SQL 7, after I successfully transfered the database, I checked the logins to see the permissions. When I went to Security/Logins there was a written message with "There are iems to show in this view, but at the top it says Logins 4 items. The logins are still there, but you can't see them. What caused this problem and what can I do to fix it?I tried to uninstall and install again, but with no luck.Thank youSQL Server 7Windows 2000" |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-04-18 : 09:56:11
|
| Do you have SP3 installed? I've had this problem a couple of times with EM, but it was always fixed simply by exiting EM and opening it right back up. You might want to try deleting the registration for the server on your client and reregistering it.Jeff BanschbachConsultant, MCDBA |
 |
|
|
Rivaldo
Starting Member
7 Posts |
Posted - 2002-04-18 : 11:02:16
|
| I am actually running XP professional, I got confused with my desktop.I tried to delete the registration and register againg, but it did not work. |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-04-18 : 11:12:23
|
| I mean SP3 for the SQL Server 7 client tools. :)Jeff BanschbachConsultant, MCDBA |
|
|
|
Rivaldo
Starting Member
7 Posts |
Posted - 2002-04-18 : 14:42:33
|
| I was not running SP3 I downloaded sp3 and installed it, nothing changed. |
|
|
|
Shawn
Starting Member
9 Posts |
Posted - 2002-04-18 : 14:55:21
|
| I do this relatively frequently (using backup and restore) as well and have the same sort of problem you are having. To top it off, you can't create new users with the same name because they exist even though nothing in EM suggests they really do.What you can do in Query Analyzer is this: USE YourDB exec sp_helpuserThis will list all users associated with the imported database. More importantly though, by looking at the LoginName column, you can see which users are "disassociated" with SQLServer logins.What I do next is: exec sp_revokedbaccess 'BadUserName'for each of the bad user names.Hope this helps. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-18 : 17:36:34
|
| Shawn,This is not the way you should handle this. It is a very common problem for users to be orphaned when a DB is moved to a new server. You should relink the user to the proper login using the sp_change_users_login Proc, or better yet, use the script I posted in the Scripts forum.-Chad |
|
|
|
Rivaldo
Starting Member
7 Posts |
Posted - 2002-04-22 : 16:15:30
|
| Chad, I tried using sp_change_users_login it gave this message:New login created.The number of orphaned users fixed by updating users was 1.I still can not see any of my Logins in the Security folder in EM, but at the top let it says Logins: 6 itemsWhere can I find your script that you posted?Thanks for the help |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-22 : 17:31:16
|
| Rivaldo,The script is here:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12615I'm not sure if this will solve your issue of not being able to see the logins though. This script was meant to remap orphaned users to logins that exist.It looks as though you are hving a different problem with SEM not showing the logins.-Chad |
|
|
|
Rivaldo
Starting Member
7 Posts |
Posted - 2002-04-23 : 10:13:49
|
| Chad: The problem is with EM, do you have any suggestions? you cand see the the explanation of the problem at the beginning of this thread.I guess I will have to unistall SQL 7.0 and then delete all instances in the registry to install again. |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-04-23 : 15:45:43
|
| If all else fails, try brute force. I find that if I delete all records with the orphaned user name from the database's systable, I can then recreate the database user from the server login. You will want to save any permissions (placing them in a role works well, since the roll travels with the database, and the user can easily be assigned to the role). |
|
|
|
|
SQL logins