Logins and users

Question

Logins and users

WILLIAM MITCHELL

SSChampion

Points: 13766
More actions
July 7, 2007 at 10:31 am

#175208

Is anybody familiar with the nitty-gritty of how 2005 handles logins and users differently from 2000?
My customer recently upgraded from 2000 to 2005. I have an Access app that provides custom reporting & some batch updating that the vendor's web app does not include. There is a login "par" and there are two db's each with a user "par" mapped to that login.
With 2000, I would take a Full backup from production & restore it to my off-site dev system, and everything worked just fine.
However, with 2005, after restoring, the login "par" still exists, and the user "par" still exists in both db's, but Access gives me a "Error 3151 ODBC connection failed" so I re-check my DSN and I get "Login failed for user 'par'".
Then I check the Login in SSMS and under User Mapping, I try to map the "par" login to the "par" users in the db's, but I get this error:
http://www.wvmitchell.com/SSCentral/create_user_failed.bmp
which says that the users already exist. I suppose I could delete the user "par" from both db's and then re-map, but then I'd have to re-assign all their permissions, correct?
What am I missing here?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

There is nothing different between 2000 and 2005.

The problem wihch you are facing sounds familar (i have done many backup and restores). I think your user in db is not mapped to login. Can you please run below command

sp_change_users_login 'Report'

if this shows you output it means there is SID mismatch. All you need to do is..

sp_change_users_login 'Auto_fix', 'user_in_db', 'login_name'

Refer books online for detailed description. ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1554b39f-274b-4ef8-898e-9e246b474333.htm

Regards,

Balmukund

sciclunam SSC Enthusiast Points: 192 More actions · Answer 2

sciclunam

SSC Enthusiast

Points: 192

July 8, 2007 at 11:52 pm

#717920

yep, that should solve it.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

Thanks for your reply. I ran that on my 2000 dev machine, for UserName "par" I get

0xF5F9569AB6284548A85AD1A73DFF927B

and on my 2005 dev machine, for UserName "par" I get

0xC899D1E7279C2942A9F82CD6204D3EB5

so I can see that the SID is different. But this problem did not present when doing backup / restore with 2000. So something is different about 2k5.

I then ran your 2nd statement, with a minor correction as per BOL:

sp_change_users_login 'Auto_fix', 'par', NULL, 'theirpassword';

in each of the two db's, and now the users are mapped correctly, the output was:

The row for user 'par' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.

Now my DSN works, but Access still cannot connect (Error 3151) and if I try to re-link it attempts to connect with user 'sa' what is the deal here?

Balmukund SSCertifiable Points: 6648 More actions · Answer 4

SID is for a login. If you create new login to server it would be generated. In SQL 2005 Server, you have to create the login and restored backup taken from SQL 2000. Is that correct. SID of sa is constant.

Sorry. I am not expert from Access part.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

create a user named par in the 2005 server then use sp_change_users_login 'update_one','par','par'

Then try logging in that hsould work.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 6

Thanks to everybody for your replies. Let's leave Access out of the equation for now, here is my specific problem with SS 2005:

The production server has two database files. There is a login 'par' and each of the db's have a user 'par' that is mapped to the 'par' login.

My off-site dev system has the same two database files. There is a login 'par' and each of the db's have a user 'par' that is mapped to the 'par' login.

The problem is that when I take a Full backup from the production system and then restore it to my dev system, it loses the mappings between the login, users, and databases. If I simply try to re-map the logins & users, it tells me that the users already exist in both db's.

Before both systems were upgraded from 2000 to 2005, I used the same steps to restore the backup to the dev system, and all of the mappings remained unchanged.

So far, the only way I can get the dev system working in the "pre-restore" state is to delete the two 'par' users, then re-create and re-map them, and then run my scripts to assign the appropriate permissions.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Check the SIDs for the logins on both your dev and production systems. If they aren't the same, export the login from production and delete and create the login on dev. That'll ensure those two are in synch, which means as you take the production backup and restore it to the dev system, everything will be okay. Here's how to do it:

How to transfer the logins and the passwords between instances of SQL Server 2005 (918992)

The key is getting the SID and creating the login with the same SID in development.

K. Brian Kelley
@kbriankelley

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 8

Cool, thanks for the tip. I'm heading over there this morning, I'll let you know how it turns out.