DBO user and sa login?

Question

DBO user and sa login?

Madiha Kashif

SSChasing Mays

Points: 608
More actions
April 21, 2006 at 7:34 am

#89925

The dbo user for many databases on my server have no login mapped on to it. It is the same in production, staging and development environment. We have set up a new development environment. Any idea why is that so? Also, if there is no login appearing in front of dbo user, I assume no but does it imply that sa is default login for that?
Coming to implications:
1. Should I leave it like that or map the dbo to sa using sp_change_dbowner.
Digging further in this, I ran the script which I caught from this site:
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'
GO
It gave me list of dbs with unmatched dbos but when I exected sp_change_users_login 'report', it did not return any records for the ones with unmatched dbos. Why the difference in results, in sense why is dbo not appearing as an orphan user.
Please doooo reply.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

users become orphanded based on the SID tied to their login.

so if you create a user 'Bob' one one server, a unique ID (SID) is assigned to that login; if a database is restored on that server from another, the 'Bob' in the database restored has a different SID than the 'Bob' in master.dbo.sysusers.

You are clearly familiar with that...sp_change_users_login changes the SID in the database t be the same as the SID in master, if the login names are the same.

The exception is is. sa has a static SID of '1', so it can never become orphaned. if you restore a database from another server, dbo has a SId of 1, , which is the same as what is in master.sysusers, so no orphaned objects can be found.

HYH.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David brenchley Valued Member Points: 68 More actions · Answer 2

I have the same issue with dbo not having a login, but when I try to use sp_change_users to fix it, I get an error

sp_change_users_login

@Action = 'Update_One',

@usernamepattern

= 'dbo', @loginName = 'sharepoint'

Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 35

Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.

David brenchley Valued Member Points: 68 More actions · Answer 3

Nevermind, I was able to find the answer

use

sp_changedbowner 'sa'

to change it back to sa

Madiha Kashif SSChasing Mays Points: 608 More actions · Answer 4

Hey, thanks for replying. I have also been using this statement. Probably, I did not post it up here or maybe against some other post. Sorry for that.