Solved

what do you about mismatched 'dbo' user

Posted on 2014-07-22
6
546 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
  • 3
  • 2
6 Comments
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 100 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Author Comment

by:25112
Comment Utility
>>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:ScottPletcher
Comment Utility
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:
ScottPletcher earned 400 total points
Comment Utility
>> 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard 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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now