SQL Server Home
Changing database owner after changing server hostname mix owner identities
as By Design
4/27/2010 2:59:06 PM
User(s) can reproduce this bug
If you change the hostname of aserver that hosts MSSQL2008 (say, Win Server 2008 Enterprise), all references to Database owners that used the old hostname stay the same. So when the MSSQL2008 try to execute anything under this user, it get an error. I think this is the best way to do it. But when you try to change the owner to a new one, with the same name, but with the new hostname, it detects as being the same users. This is an easier to understand error in the "Steps to reproduce" box.
SQL Server 2008 - Express Edition
Windows Server 2008
Operating System Language
Steps to Reproduce
1. Install Win Server 2008 Enterprise with hostname "A".
2. Install MSSQL2008 and make "A\Administrator" the owner of database "X".
3. Change Win Server 2008 Enterprise hostname to "B".
4. Check that the owner of "X" is still "A\Administrator". Anything that depends on "xp_logininfo" will throw an exception saying that it cannot find "A\Administrator". Maybe this is good (you could think that it could check about Windows credentials).
5. Try to add user "B\Administrator", so you can change the "X" database owner.
An exception is thrown warning that user "B\Administrator" is already an user, which is not true. The only user is "A\Administrator".
Or all "A\Administrator" users should be automatically substituted for "B\Administrator" or no exception should be thrown. That is, "A\Administrator" is "B\Administrator" or not. I don't know, but maybe this is a security issue, where changing hostname may confuse security layer when confronting credentials.
to post a comment.
Please enter a comment.
on 6/14/2010 at 8:07 PM
Referencing identities by SID has a number of advantages over using the string representation which is why products such as SQL Server and Windows use it to identify principals. Although it would be nice if SQL Server could automatically change the string representation whenever the hostname changes, it becomes difficult to do because SQL Server has not way of being notified when a change occurs. In addition, such system changes are normally rare enough that it would be problematic in very few cases, and even then, changing principal names automatically is not something that we would want to do and we would want it to be explicit (e.g., it could otherwise break scripts, etc.)
on 6/14/2010 at 7:29 AM
Thank you for answer Mr. Il-Sung. I understand that this is by design. Shouldn't it be better if that would not be referenced by SID, or that the SID would get updated at hostname change? Why bother the responsible for the system with such thing, if it can be done automagically? There would be no reason to keep "A\Admin" over "B\Admin".
Daniel Scain Farenzena
on 5/11/2010 at 11:44 AM
Thanks for submitting your feedback. The reason that you're seeing this behaviour is that internally, SQL Server references all principals by their SID so A\Administrator and B\Administrator would appear to be the same principal since the SID would already exist in the server metadata. Rather, you can simply alter the login to change the friendly name of A\Administrator to B\Administrator and that should make everything appear consistent. i.e.,
ALTER LOGIN [A\Administrator] with name = [B\Administrator]
Hope this helps,
to post a workaround.
Please enter a workaround.
© 2014 Microsoft