mapping users with the login

Question

mapping users with the login

Maverick100877

SSCertifiable

Points: 5240
More actions
May 30, 2007 at 10:04 pm

#76717

Hi,
A database was restored from backup to a new server. Now the database has users in it. Now there is a user lets say arun which is having windows authentication. Now since this has to be associated with the login arun then only the user can log on. Now when I create the login and grant it access to the database it says that user already exixts in the database. When i try to map the user using
sp_change_users_login 'update_one','arun','domain\arun'

I get error. It says that username arun is absent or invalid.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

To get this to work create the user in security and do not grant access to the DB. It will casue the issue you are seeing and it only means the usaer exists in the DB not the system.

Once you have created the user in the system with default db of master you can then run the script and will work. Afterwards you may want to adjust any properties on the systems account such as default DB if you had set differently.

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 2

Maverick100877

SSCertifiable

Points: 5240

May 31, 2007 at 8:31 am

#710018

Will this work for a windows login also.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

yes, it is a matter of mapping them into sqls logins first then attaching them to the db via sp_change_users_login. The user account in the db is technically an alias is all.

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 4

it is not mapping the nt user.

I tried to map it to sql login also but it also failed with the error that the user does not exists.

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

Hi,

We did the following steps. But for nt user we had to map it manually.

Source Server

==============

1. Back up all the Databases

2. Run the Stored Proc as in KB :http://support.microsoft.com/kb/918992/

3. Save the output of the EXEC sp_help_revlogin in the source server -- Login.txt

4. Run select * from syslogin

5. Save the result in a text file example :Info_login.txt

Target Server

================

1. Restore the Databases

2. Run the Login.txt

3. Check the Info_login.txt

4. Give the permission accordingly

Regards,

Minaz

"More Green More Oxygen !! Plant a tree today"

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 6

I want to know how you mapped the nt logins manually. I'm not able to do that.

When I'm trying to dd the windows login and making the default db as the one in which the user exists it is not letting me create the user.

So I create the login and let the master db be the default db.

Now when i try to associate this login to the already existing user using sp_change_users_login gives error that the user does not exist.