RE: Error in SQL Enterprise Manager after migrating a user & their WS

[Rebecca Chen [MSFT]]

Hi Joe,

Actually, it is a SQL Server related issue. Please post SQL Server related
issue to the following newsgroup: Microsoft.public.sqlserver

According to my experience, this is an expected behavior. This SQL Server
register information is stored in the Registry key;

HKCU\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered
Servers X

IF the users have not altered their configuration to store it on a remote
server, the restoration of these Registry Keys is not something we support,
however I was able to provide a VB Script for your reference:

Servers X

An application can be written using SQL-DMO's RegisteredServers object to
automate the process of collecting this information for each Enterprise
Manager user so that when something like a domain change occurs, the
application can then re-register the SQL Server instances.

The RegisteredServer object exposes the attributes of a single,
registry-listed instance of Microsoft SQL Server. Once this information is
collected for by the user running the application, they could run the
application again to register these servers at a later time.

Here is a sample VB Script using the RegisteredServers Collection in
SQL-DMO:

NOTE: Script is not supported in this newsgroup. I only provide the code
for your reference.

Also, in this sample application, the application retrieves the username
and password if the registration is done using SQL Authentication, it
stores values to a table in text (readable format) and could potentially
cause security issues.

Here are the steps:

1. Create a vbs file and call it readreg.vbs which contains the following.
Please substitute <servername> with SQL Server name and <password> with the
SA password.

It also creates a table RegServers in pubs database so make sure that this
does not exist. This script collects information about the currently
registered servers and stores the information in the RegServers table.

' turn on this to trap any syntax or declaration errors Option Explicit

' beginning of routine
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput

set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups

oSQLServer.Connect "<servername>", "sa", "<password>"
oSQLServer.ExecuteImmediate

"Create table pubs..RegServers(ServerName varchar(30), SLogin varchar(30),
SPassword varchar(30), IsNtAuth varchar(2))"

For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next

oSQLServer.Disconnect

2. To run, open a command-prompt and run "cscript readreg.vbs".

3. To verify that the information was retrieved correctly, go to the SQL
Server
specified in the script and run the following:

use pubs
select * from RegServers

4. Create another vbs file and call it createreg.vbs which contains the
following:

Again substitute <servername> with SQL Server name and <password> with the
sa
password.

' turn on this to trap any syntax or declaration errors
Option Explicit

' beginning of routine
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim MsgOutput

set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups

oSQLServer.Connect "<servername>", "sa", "<password>"
oSQLServer.ExecuteImmediate

"Create table pubs..RegServers(ServerName varchar(30), SLogin varchar(30),
SPassword varchar(30), IsNtAuth varchar(2))"

For Each oServerGroup in oApplication.ServerGroups
For Each oRegisteredServer in oServerGroup.RegisteredServers
MsgOutput = "Insert Into Pubs..RegServers values("
MsgOutput = MsgOutput + "'" + oRegisteredServer.Name + "', '" +
oRegisteredServer.Login + "', '" + oRegisteredServer.Password + "', '" +
CStr(oRegisteredServer.UseTrustedConnection) + "')"
oSQLServer.ExecuteImmediate MsgOutput
Next
Next

oSQLServer.Disconnect

2. To run, open a command-prompt and run "cscript readreg.vbs".

3. To verify that the information was retrieved correctly, go to the SQL
Server
specified in the script and run the following:

use pubs
select * from RegServers

4. Create another vbs file and call it createreg.vbs which contains the
following:

Again substitute <servername> with SQL Server name and <password> with the
sa
password.

' turn on this to trap any syntax or declaration errors
Option Explicit

' beginning of routine
Dim oApplication
Dim oServerGroups
Dim oServerGroup
Dim oRegisteredServer
Dim oNewRegisteredServer
Dim oSQLServer
Dim oQueryResults
Dim MsgOutput
Dim num
Dim SrvNameOutput
Dim LoginOutput
Dim PassOutput
Dim AuthOutput
Dim GetLoginOutput

set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oApplication = CreateObject("SQLDMO.Application")
Set oServerGroups = oApplication.ServerGroups oSQLServer.Connect
"<servername>",
"sa", "<password>"

set oQueryResults = oSQLServer.ExecuteWithResults ("Select ServerName,
SLogin,
SPassword, IsNtAuth from pubs..RegServers") For num = 1 To
oQueryResults.Rows
SrvNameOutput = oQueryResults.GetColumnString(num, 1)
LoginOutput = oQueryResults.GetColumnString(num, 2)
PassOutput = oQueryResults.GetColumnString(num, 3)
AuthOutput = oQueryResults.GetColumnString(num, 4)

If LoginOutput = "" Then LoginOutput = " "
Set oNewRegisteredServer = CreateObject("SQLDMO.RegisteredServer")
oNewRegisteredServer.Login = LoginOutput
oNewRegisteredServer.Name = SrvNameOutput
oNewRegisteredServer.Password = PassOutput
oNewRegisteredServer.UseTrustedConnection = CLng(AuthOutput)
oServerGroups("SQL Server
Group").RegisteredServers.Add(oNewRegisteredServer)
Next

oSQLServer.Disconnect

5. To run, open a command-prompt and run "cscript createreg.vbs".

NOTE:
a. When doing the tests, please make sure that Enterprise Manager is
closed.
b. The script gathers information about the registered servers for the
currently

logged on user.
c. If you want to test on a single machine, you may do the following:
- run readreg.vbs
- Delete the registration from Enterprise Manager or delete the entries in
the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL
Server\80\Tools\SQLEW\Registered

Servers X

Please be warned that modifying the registry can cause serious damage to
the computer and may even cause a reinstallation of the operating system.
- run createreg.vbs - open Enterprise Manager and check the registration

Further questions, please post it to SQL server newsgroup.

Have a great day!

Best regards,

Rebecca Chen

MCSE2000 MCDBA CCNA

Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security

=====================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.