April 29, 2008 at 9:43 am
I changed the name of a server due to DR using sp_addserver, this has changed the name in sys.servers, but I still notice that when I run a trace and select server name that the old server name appears.
I also have an odd problem, this might be seperate, but on a couple of databases that a user has read write to they can't access tham and get a login failure but if I give them sysadmin they can access them!! Baffles me, I thought it might be orphaned users but I removed the login from the database and added it again plus it has not happened on other dbs on this instance or the other instance I recovered.
Anyone able to shed any light or need more info or had similar?
Thanks
April 29, 2008 at 9:50 am
Did you check the sys.sysservers table in master?
April 29, 2008 at 9:59 am
Yes, when you rename you drop the server which removes the entry from sys.servers and then add the server as 'local' which puts it back in sys.databases as the local machine. This is what BOL tells you to do anyway, if there are any additional steps I don't know about them and have so far failed to find any.
April 29, 2008 at 10:51 am
The new name will take effect after you stop and restart your server! Did you forgot that part?
Sopheap
April 30, 2008 at 2:00 am
No, it's been restarted, all users can connect except for these two databases where they can connect but only via sysadmin.
The main problem is that if I run a trace it still gives me the old server name but in sys.servers the name was changed, I want to know where else the server name could remain and if there are any other steps appart from those mentioned in BOL I need to run.
April 30, 2008 at 5:26 am
First of all did you use sp_dropserver to remove the server and then add the new server?
Secondly, if you are trying the operation for the local server, there might be remote logins or linked servers so the process was not successful?
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 30, 2008 at 5:59 am
sometimes msdb could be hardhead... I sometimes have to manually update one of the table systasks? to say the new name instead of local.
Sopheap
April 30, 2008 at 7:00 am
I can't locate systasks in msdb, is it in 2005?
April 30, 2008 at 7:03 am
Chandrachurh Ghosh (4/30/2008)
First of all did you use sp_dropserver to remove the server and then add the new server?Secondly, if you are trying the operation for the local server, there might be remote logins or linked servers so the process was not successful?
The rename using the instructions from BOL was successful, when I look at sys.servers all the entries are correct, on one instance I do have linked servers but the addserver process has successfully added the new server as local, on the other instance there are no linked servers so there is only one entry in sys.databases which is correct.
The main problem is why when I do a trace does the old server name appear, where is this stored and why am I getting some odd login failures as metioned above. Users can see the new server and can connect.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply