SQL Server User mapping vanished

Question

SQL Server 2008 web edition x64 I'm opening: (connection)/Security/Logins/John. Select "User Mapping'. On right pane is marked checkbox at DB1 line. I turn on checkbox at DB2 line, correct username and schema appear. Press 'ok'. Opening (connection)/Security/Logins/John once more and there is mo mark at DB2 checkbox, it's clear. No username/schema at that line, of course. Mark checkbox again, and so on until become mad.

No error messages, no errors in log.

I can't turn off DB1 checkbox because of 'Canno drop the user dbo'

DB1 really doesen't importanf for me, but I need DB2 mapping. How can I fix this?

Answer 1

I'm not sure if the issue is difficulty connecting a user to the login on database(db2) or if you are having difficulty unassociating the user with the schema dbo on database(db1).

It sound to me like the larger issue is connecting the user to db2. If the user already exists on db2 you can associate them with the login using the command

EXEC sp_change_users_login ‘UPDATE_ONE’ , ‘sqlusername’, ‘sqlloginname’;

If the user doesnt exist in db2 you can create them using

CREATE USER 'username' FOR LOGIN 'loginname' 
WITH DEFAULT_SCHEMA = 'default schema name';