Orphand users problem

Question

Orphand users problem

Mani-584606

SSChampion

Points: 11788
More actions
July 2, 2009 at 4:11 pm

#384904

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??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

There are a couple of other actions that sp_change_users_login can use besides report... these just happen to fix the orphaned user. Check out BOL for the procedure.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQL Dev-938873 Ten Centuries Points: 1280 More actions · Answer 2

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(':-)');

GaNeSH

ps. One Orange Chip Points: 29422 More actions · Answer 3

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