Changing database owner after changing server hostname mix owner identities - by d.scain.farenzena

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 554649 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 4/27/2010 2:59:06 PM
Access Restriction Public


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.
Sign in to post a comment.
Posted by Microsoft on 6/14/2010 at 8:07 PM
Hi Daniel,

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.)

Posted by d.scain.farenzena 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".

Best regards,
Daniel Scain Farenzena
Posted by Microsoft 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,