|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 264,
Visits: 800
|
|
Hi,
We have a prodution sql server 2000 and have only 2 Logins(SQL logins No windows logins) ABC and XYZ. We have 5 databases and in each database has 8 users and more(database->security->users), out of these 8 users, 2 users are ABCand XYZ and other 6 users as below:
scott, Jim, smith,alex, andrew and peter.
I did not understand how these users were created without having Logins related to that users?
Is this possible to create Users without creating Logins?
We have upgrated this sql server 2000 to 2005 and I used sp_help_revlgin procedure to script out the logins and ran the generated output in sql server 2005.
I checked for the Orphend users by executing sp_change_users_login 'report' in each database
and in every database, user smith resuted as Orphend user.
and in one databse, user XYZ (which is a login)is also resulted as Orphend user.
How to fix these orphend users problem??
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 12:19 PM
Points: 1,261,
Visits: 1,501
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Wednesday, November 04, 2009 3:05 AM
Points: 46,
Visits: 197
|
|
First, make sure that this is the problem. This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
Hope u got the info..
Reply me if u don't get the solution
all the best..txtPost_CommentEmoticon(' ');
|
|
|
|
|
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 5:41 AM
Points: 1,698,
Visits: 2,267
|
|
Mani (7/2/2009)
I did not understand how these users were created without having Logins related to that users?
Is this possible to create Users without creating Logins?
This happens usually when you restore a database. Remember that logins are stored in the master database where as user IDs are stored in the user databases. When you restore a database, the user ids stored in the database are restored where as their respective logins are still missing from the master database.
Previous posts clearly states on how to resolve the issue using sp_change_users_login.
Pradeep Singh
|
|
|
|
