| Subject: |
Fix logins after an "Attach database" in MS SQL |
| Updated: |
Oct-20-2005 |
| Rating: |
Not Rated |
| By: |
Rocky Moore - Member #: 1 |
| Location: |
Klamath Falls, Oregon USA |
| Website: |
www.RockyMoore.com |
| Category: |
Computers > Programming > Databases |
If you ever have to "attach" a database that you have copied from another instance of SQL Server, you may find that your login information does not exist but when you try to create the missing logins, it errors out saying they already exist.
To solve the problem, select that database and execute:
sp_change_users_login 'Auto_Fix', 'loginAccountToFix'
Simply change teh 'loginAccountToFix' to the name of the login that is missing. After you run this you will proably have to set the password on this login again as it will probably be set to null.
Update:
For SQL Server 2005, you need to specify the user name, login name and password. If the user name and login name are the same, you can use a NULL as:
sp_change_users_login 'Auto_Fix', 'loginAccountToFix', NULL, 'password'
[Submit DIGG for this tip]
-----------
Note: Use the tips posted on this site at your own risk.
The tips are posted by the public and as such may or may not be valid.