Solved

what do you about mismatched 'dbo' user

Posted on 2014-07-22
6
638 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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