Login Rejected

Hello,I develop my application on two different workstations (one at work and one at home). On each, I created a database with the same name, and added identical users with the same login ID and password. For simplicity, let's just say the database is called "my_data", and the user is "my_user" with a password of "12345". Recently, for reasons beyond this post, I needed to copy the data from my work database to my home database. I tried both a Detach Database, and a Full Database Backup, and have gotten the following result: When I attach/restore the databases to my home computer, the login "my_user" and "12345" fails. I THINK that even though my databases have users with the same login ID and password in them, that the two users are actually DIFFERENT users because they were created on different servers. I tried to remove the user from the database, but the db server wouldn't let me since the user owned a schema (which I can't uncheck in user preferences because it is grayed out). So I think I need to figure out how to remove the user that is in the restored database, and re-add the user that exists on the server that the database was restored to. Any suggestions? How do I remove the owned schema from the user that is in the database? Thanks! Mike

Hi,

when you use a SQL Database User (not an active directory user), the SQL Server generates SID's for the users. In your case the SQL Server generates two different SID's for the user. You can try to use the following stored procedure to synchronise the SID's:

- sp_change_users_login 'report' -> Displays all missing users
- sp_change_users_login 'Auto_Fix', 'HERE_USER_NAME', NULL, ''

See the msdn site (http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx) for more information.

Regards
Marc André