Monday, February 4, 2008

Maps an existing database user to a SQL Server login using SP_CHANGE_USERS_LOGIN

Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.


EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'

- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.

AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.


extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction

17 comments:

mysok said...

Hello,
thanks a lot! It's a really usefull solution for "fixing" users from restored database.
Unfortunately there are users in the restored database who cannot be fixed or removed. For example user "sys" from MSSQL2005 (backup from), this user isn't in MSSQL2008 (restore to) and it cannot be removed from restored database security. Or yes?
Hi MP

Jonathan said...

WoW nice blog buddy!Actually WoW is my favorite appreciation keyword. I also like to play. Wow Gold. I was looking for info regarding that and saw your website. Its nice and very interesting.

lala said...

Thanks for your post and welcome to check: here.

Dheeraj said...

nice post very well written. i appreciate quality of writing u haveSEO Company India

Aaliyah sam said...

I would like to thank you for the efforts you have made in writing this post. I am hoping

the same best work from you in the future as well.

Frases de Amor

frankleo said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit
Plumbing Greenville

sami smash said...

I would like to thank you for the efforts you have made in writing this post. I am hoping

the same best work from you in the future as well.

judi online

jimmy said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit

Houston Electrician

vikas kukreja said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit

Missouri City Electrician

smash john said...

I would like to thank you for the efforts you have made in writing this post. I am hoping

the same best work from you in the future as well.

Reliable VPN Proxy Service

josef den said...

I would like to thank you for the efforts you have made in writing this post. I am hoping

the same best work from you in the future as well.

windows vps server

harrywatson said...

Nice stuff you got, very interesting to read.
Well, I do have also in my sleeves, if you have time don't forget to visit

cheap voip call

john ibrahim said...

I would like to thank you for the efforts you have made in writing this post. I am hoping

the same best work from you in the future as well.

compra venta

micheal j said...

I would like to thank you for the efforts you have made in writing this post. I am hoping

the same best work from you in the future as well.

electrician in phoenix

vish jim said...

I would like to thank you for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well.

video production companies

charlie smash said...

I would like to thank you for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well.

Escorts San Diego

Calvin Brock said...

Children and the elderly are the most susceptible to burns because their reaction time to scalding water is slower. Small children don't know how to remove themselves from the threat of scalding water either. Plumbers World