Solved

what do you about mismatched 'dbo' user

Posted on 2014-07-22
6
595 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

742 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