April 21, 2006 at 12:01 pm
Nobody replied in other forum so I am trying putting my questions on this forum. Here it goes:
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. I think they were Windows users.
Please doooo reply.
April 21, 2006 at 12:40 pm
Madiha,
Your #1 (sp_changedbowner) will help. I always run sp_changedbowner 'sa' (or other login name) when I have a similar situation.
Why did it happen? Developers mostly connect by default through their Enterprise Managers so it is their Windows login who is a database owner (dbo) when moving the database to another server, if this Windows login was a local login or a login from another domain its SID could not be resolved. In the database properties it will say Owner - Unknown. Same with SQL Standard login.
Another solution is to create an SQL Standard login on all servers - development, test and production with the same SID. You do that by using sp_addlogin, using @sid parameter
Regards,Yelena Varsha
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply