Solved

what do you about mismatched 'dbo' user

Posted on 2014-07-22
6
616 Views
Last Modified: 2014-07-31
when you restore a db on another server,
EXEC sp_change_users_login 'Report'
reports the mismatched sid values.....

but trying to fix 'dbo' value gives
"Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure."

how can this mismatched sid for 'dbo' affect the application in a negative way? would you be concerned about it?
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 100 total points
ID: 40213663
You don't have to worry abot the dbo role. It is only the SQL logins (with SQL authentication) that need to be "repaired". What happens is this:

You have one server with the logins. On this server when you create an SQL authentication login it will be given an ID(GUID) that is unique. When you map the login/user with a database to give it access that ID will follow the user in that database. When you restore the database on a different server the user/login with its original ID will be restored on the second server as it is part of that database. If that login/user doesn't exist on the second server you will have to create it. Th eproblem is that when you do that it will get a different ID which will not match the one on that you restored with the database. In order to match them you will have to use the repair feature. What happens in the process is that the login and its corresponding in the database will be paired by name and the ID will be updated to be the same. Now the user is fixed. With the domain logins/users(windows authentication) this will never happen because their ID will be the same and at the network level so every time you add them ti a new server they will keep their ID.

The role only exist at the database level so they don't need to be repaired.
0
 
LVL 5

Author Comment

by:25112
ID: 40214310
a)
>>The role only exist at the database level so they don't need to be repaired.

is that why
select * from sys.syslogins where name = 'dbo'
brings back nothing?

b)
when
EXEC sp_change_users_login 'Report'
reports
"dbo"
as the only record, where is it comparing and reporting a concern?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40214747
I strongly disagree.

The dbo user can still have a corresponding login, and it's extremely important that the dbo user not be "orphaned".

If 'dbo' gets reported, I suggest changing the db owner to sa to prevent possible future failures due to an invalid/missing db owner:

ALTER AUTHORIZATION ON DATABASE::<db_name> TO sa


My preference is for as many native SQL users as possible, to sync up the sids on the different environments (dev, qa, prod, etc) so I don't have to constantly repair sids after restoring a db from one environment to another.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 5

Author Comment

by:25112
ID: 40214906
>>it's extremely important that the dbo user not be "orphaned".

in my case (example), it is then orphaned? is it mainly orphaned because it does not have corresponding login? can I or should I create a dbo login and then try to repair with sp_change_users_login?

or is changing the owner the only solution?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40214962
You could create the corresponding login and re-link the user and login.

I prefer that all my dbs be owned by sa, when sa is available.

But certain apps do require that their login owns dbs directly.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 40215343
>> is it mainly orphaned because it does not have corresponding login? <<

That should be what is causing it to be reported by that proc.


>> can I or should I create a dbo login and then try to repair with sp_change_users_login? <<

The login name won't be 'dbo', it will be something else.  'dbo' is a special user name that does not correspond to a login name, that special user instead just literally owns the database.

For example, 'sa' can be the 'dbo' user, 'yourdomain\user1' can be 'dbo', etc..
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question