How to change computer name in SQL 2005

Question

How to change computer name in SQL 2005

maranbe

Mr or Mrs. 500

Points: 586
More actions
January 10, 2007 at 10:03 pm

#174438

Hi Folks,
I would like to change the name of my server that houses SQL 2005. As I understood it in SQL 2000 you still have to run the setup again, how about in SQL 2005? Would appreciate any help. Thanks.
Kindest Regards,
SQL 2000 (Clustered)/SQL 2005

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

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

there's a difference between changing a machine name, and changing a named instance.

for example, if my machine is named DEVPLATFORM, and I want to rename it as DEVELOPER, I right click on my computer, select properties...Network Id tab and change the machine name.

After that has been done, i run the following commands to affect my SQL instance:

sp_dropserver 'old server name'

sp_addserver 'new server name', 'local'

or specifically in this example:

sp_dropserver 'DEVPLATFORM'

sp_addserver 'DEVELOPER', 'local'

now, my machine will be visible to other machines as DEVELOPER, and when they run select @@SERVERNAME in a query, it will return the correct name.

also, if I had any instances of SQL, such as DEVPLATFORM\SQL2005, they will now need to connect to DEVELOPER\SQL2005

now if you were talking about renaming the instance DEVPLATFORM\SQL2005 to be DEVPLATFORM\NEWNAME, that requires deleting one instance and adding a new one;

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!

maranbe Mr or Mrs. 500 Points: 586 More actions · Answer 2

Thanks for the quick reply. What happens now with the "default" login users created during the installation that uses the old machine name?

Kindest Regards,

SQL 2000 (Clustered)/SQL 2005

Lowell SSC Guru Points: 323482 More actions · Answer 3

there is no change for the users; remember that their logins and permissions are mapped to roles in each of databases on the machine. Esentially their permissions are stored in the master database and are related to login name & SID for mapping permissions. They are not related to a machine name.

It's similar to connecting by IP address: the ip address may have changed, but once connected, the permissions associated to the login remain the same.

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!

maranbe Mr or Mrs. 500 Points: 586 More actions · Answer 4

in my logins, i have found these users (see below). "X226" is my old machine name which i already changed to a new name. Would it affect the system if i delete these users created during the installations?

X226\SQLServer2005MSFTEUser$X226$MSSQLSERVER

X226\SQLServer2005MSSQLUser$X226$MSSQLSERVER

X226\SQLServer2005SQLAgentUser$X226$MSSQLSERVER

Kindest Regards,

SQL 2000 (Clustered)/SQL 2005

Summer90 SSC-Dedicated Points: 32864 More actions · Answer 5

Hey, that is a very good question. I renamed a SQL 2005 and ran the SP_drop / add and it all worked fine. However, those three internal IDS still have the old server name in them. I don't see anything in BOL about renaming a SQL Server on what to do about them ....