Solved

what do you about mismatched 'dbo' user

Posted on 2014-07-22
6
577 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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 …

828 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