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';

• Putnik: Thank you. I want to connect user to DB2. EXEC sp_change_users_login gave no results. Checkbox at DB2 row is off. I tried to mark it again and save, but it wasn't save without any errors. Any ideas?
• Putnik: User in DB 2 exist and on the property page I can see grayed selector "login name/certificate name/key name/without login". It is marked as "login name" and has correct text in grayed textbox field.
• Lumpy: so properties page for the user on db2 has the user associated with the correct login. properties of the login does not show the user and schema for db2.
• Lumpy: try running this query on db2. This should show what users are associated with which logins on that db. If it comes back correct I'm not sure why the login properties page isnt picking it up. I can't reproduce it over here. select l.loginname as [login name],u.name as [user name] from sysusers u inner join master..syslogins l on u.sid=l.sid
• Putnik: Thank you. Would you so kind to look at one more question?