May 4, 2020 at 12:27 pm
Hi
Got this weird issue after a server was brought to live due a VM issue.
A domain user, let's call it CONTOSO\joe is not able to do anything on the database "ABC". After checking the database, I see no user. I attempted to create the database user and got an error saying the user already exists. Long story short, CONTOSO\joe Login is mapped to "dbo", it is NOT mapped to a database user with the same name, as it suppose to be.
How can we fix this? Creating a user is not possible and I can't delete "dbo".
Is there any way to remap CONTOSO\joe to a corresponding database user?
Thanks
May 4, 2020 at 1:45 pm
So is CONTOSO\joe the database owner? Use ALTER AUTHORIZATION to change the owner. I can't remember whether that will remove your user from the database altogether - if it does, you'll need to recreate the user if you want him to continue to have access.
John
May 4, 2020 at 2:38 pm
Can you please tell me the exact syntax for the ALTER command?
By the way... I forgot to mention this... this issue is on SQL2016 and SQL2008 boxes. Many boxes went down due a major IT problem and now we are seen this login / user mismatch.
CONTOSO\joe exists at Server level (Login)
CONTOSO\joe db username does NOT. But when you check "dbo" it is mapping to CONTOSO\joe.
I cannot create CONTOSO\joe database user because it says that is there. But I cannot delete it either, because is not actually there either.
May 4, 2020 at 3:16 pm
See example F here.
John
May 4, 2020 at 6:27 pm
How was this system created - is it a new system where you restored the databases from backups? If so - check the owner of the database and change it to an appropriate login and that will take care of the problem with the user.
The next problem is the orphaned users - which happens if you create a new login with the same name as an existing user. The user does not have the same SID as the login so they don't match. To fix this, either create the login with the correct SID or use sp_change_users_login to update the user SID to match the login SID.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 4, 2020 at 9:36 pm
No, this is not about orphaned users. The Login does not match the db user, that's the problem here.
It is an old system... old are old, running on VMs but there was a major issue that crashed the VMs and the databases were restored via backups.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply