Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Hidden Dangers! Expand / Collapse
Author
Message
Posted Friday, April 30, 2004 3:20 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/hiddendang


Post #113982
Posted Tuesday, May 4, 2004 4:08 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743

Note: Until it is updated there is a correction to the code.

dim App, cntServ, cntGrps

Set App = CreateObject("SQLDMO.Application")

For cntGrps = 1 to App.ServerGroups.Count
    For cntServ = 1 To App.ServerGroups(cntGrps).RegisteredServers.Count
  MsgBox App.ServerGroups(cntGrps).Name & " - "_
    & App.ServerGroups(cntGrps).RegisteredServers(cntServ).Name & " - "_
    & App.ServerGroups(cntGrps).RegisteredServers(cntServ).Login & " - "_
    & App.ServerGroups(cntGrps).RegisteredServers(cntServ).Password
 Next
Next

Set App = nothing




Post #114221
Posted Tuesday, May 4, 2004 10:22 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 18, 2005 8:25 AM
Points: 37, Visits: 1

As usual, great article James, Good Job!

I finished reading it and it let me so intrigued that I decided to give it an additional twist to the code and try to port it to a stored procedure (using sp_OA.. procedures to instantiate the DMO COM object model). This is the result

CREATE PROC sp_CheckGroups AS
BEGIN
DECLARE @intReturnCode   int,
  @intDMO     int,
  @intServerGroups  int,
  @intGroupTotal   int,
  @intGroupCount   int,
  @intServerGroup   int,
  @intRegisteredServers int,
  @intServerTotal   int,
  @intServerCount   int,
  @intRegisteredServer  int,
  @strTmpChar    varchar(100),
  @strServerGroupName  varchar(100),
  @strServerName   varchar(100),
  @strServerLogin   varchar(100),
  @strServerPassword  varchar(100),
  @strDoing   varchar(100),
  @strSource   varchar(100),
  @strDescription  varchar(100)
  
 SET @strDoing = 'Creating the DMO object'
 EXEC @intReturnCode = master.dbo.sp_OACreate 'SQLDMO.Application', @intDMO OUTPUT
 IF @intReturnCode <> 0
  GOTO Error
 
 SET @strDoing = 'Creating the ServerGroups object'
 EXEC @intReturnCode = master.dbo.sp_OAGetProperty @intDMO, 'ServerGroups', @intServerGroups OUTPUT
 IF @intReturnCode <> 0
  GOTO Error

 SET @strDoing = 'Counting Registered Groups'
 EXEC @intReturnCode = sp_OAGetProperty @intServerGroups, 'Count', @intGroupTotal OUTPUT
 IF @intReturnCode <> 0
  GOTO Error

 SET @intGroupCount = 1

 WHILE @intGroupCount <= @intGroupTotal
 BEGIN
  SET @strDoing   = 'Creating the Server Group'
  SET @strTmpChar = 'Item(' + CAST(@intGroupCount AS VARCHAR) + ')'
  EXEC @intReturnCode = sp_OAMethod @intServerGroups, @strTmpChar, @intServerGroup OUTPUT
  IF @intReturnCode <> 0
   GOTO Error

  SET @strDoing = 'Creating the RegisteredServers object'
  EXEC @intReturnCode = master.dbo.sp_OAGetProperty @intServerGroup, 'RegisteredServers', @intRegisteredServers OUTPUT
  IF @intReturnCode <> 0
   GOTO Error
 
  SET @strDoing = 'Counting Registered Servers'
  EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServers, 'Count', @intServerTotal OUTPUT
  IF @intReturnCode <> 0
   GOTO Error
  
  SET @intServerCount = 1
   
  WHILE @intServerCount <= @intServerTotal
  BEGIN
   SET @strDoing    = 'Creating the Registered Server'
   SET @strTmpChar  = 'Item(' + CAST(@intServerCount AS VARCHAR) + ')'
   EXEC @intReturnCode = sp_OAMethod @intRegisteredServers, @strTmpChar, @intRegisteredServer OUTPUT
   IF @intReturnCode <> 0
    GOTO Error   
  
   SET @strDoing = 'Get the Name Property of The Group'
   EXEC @intReturnCode = sp_OAGetProperty @intServerGroup, 'Name', @strServerGroupName OUTPUT
   IF @intReturnCode <> 0
    GOTO Error

   SET @strDoing = 'Get the Name Property of the Registered Server'
   EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Name', @strServerName OUTPUT
   IF @intReturnCode <> 0
    GOTO Error

   SET @strDoing = 'Get the Login Property of the Registered Server'
   EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Login', @strServerLogin OUTPUT
   IF @intReturnCode <> 0
    GOTO Error

   SET @strDoing = 'Get the Password Property of the Registered Server'
   EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServer, 'Password', @strServerPassword OUTPUT
   IF @intReturnCode <> 0
    GOTO Error

   PRINT 'Group Name: ' + @strServerGroupName + CHAR(13) + 'Server Name: ' + @strServerName +
    CHAR(13) + 'Server Login: ' + @strServerLogin + CHAR(13) + 'Server Password: ' +
    @strServerPassword

   SET @intServerCount = @intServerCount + 1

   SET @strDoing = 'Destroy Registered Server Object'
   EXEC @intReturnCode = master.dbo.sp_OADestroy @intRegisteredServer
  END
  SET @intGroupCount = @intGroupCount + 1

  SET @strDoing = 'Destroy RegisteredServers Collection'
  EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroup

  SET @strDoing = 'Destroy Registered Group Object'
  EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroup
 END

 SET @strDoing = 'Destroy ServerGroups Object'
 EXEC @intReturnCode = master.dbo.sp_OADestroy @intServerGroups

 SET @strDoing = 'Destroy DMO Object'
 EXEC @intReturnCode = master.dbo.sp_OADestroy @intDMO

 -- IF we get here the normal way, don't do error
 GOTO Cleanup

Error:
 EXEC sp_OAGetErrorInfo @intDMO, @strSource OUTPUT, @strDescription OUTPUT

 PRINT 'Error While ['  + ISNULL( @strDoing     , '' ) +
   '], Source ['  + ISNULL( @strSource     , '' ) +
   '], Description [' + ISNULL( @strDescription     , '' ) + ']'   
 
Cleanup:
END

The procedure works OK, but when I run the script (as a .vbs file) I obtain 8 registered server (versus only one when I run the stored procedure). Do you see anything wrong on the SP? Can anybody here reproduce this strange behaviour?

Keep writing this great articles, I really enjoy them!




Post #114312
Posted Tuesday, May 4, 2004 12:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 15, 2005 6:30 AM
Points: 90, Visits: 1

I would like to understand how this is working in the first place. Does anyone have a minute to tell me more about how it works.

Also I noticed that it will pick up any servers you have registered and will not pick up the 'local' server if it is noted possibly in the registry as such.

 




Linda B
Post #114329
Posted Tuesday, May 4, 2004 1:46 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
It's going to boil down to where it is executing from. For instance I do it on a Dev Ed on my machine here locally and I get the data in my EM. However if I put on a remote server it will be that of the EM installed on the remote server. Keep in mind local of code execution and security context may have some influence but I am usin my own login as the account for the SQL Service here.


Post #114355
Posted Tuesday, May 4, 2004 1:48 PM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Monday, October 20, 2014 1:24 PM
Points: 8,370, Visits: 743
Which are you refering to? The aritcle or the above SP code? Is there something specific you question. As for "will not pick up the 'local' server if it is noted possibly in the registry as such" it will if you have labled as local otherwise it will be the name registered in EM (which the data is stored in the registry).


Post #114357
Posted Tuesday, May 4, 2004 2:40 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 18, 2005 8:25 AM
Points: 37, Visits: 1

The strange thing I notice is that both the script and my stored procedure are run against the same server and they show me different results. I'm using the same credentials (sysadmin) to run both the procedure and the script (using windows autentication) so the security context is the same too. So why the resultset is different?

In other words

RegisteredServers.Count (equals 8 in the script)

EXEC @intReturnCode = sp_OAGetProperty @intRegisteredServers, 'Count', @intServerTotal OUTPUT (equals 1 in the stored procedure

Why?




Post #114371
Posted Wednesday, May 5, 2004 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 11:05 AM
Points: 2,636, Visits: 789

Wow, who would have thought?

I know I raise this point every time someone writes something like this:

A user who saves any password is as scary as the ones who write them on sticky note nearby. 

but, what are you supposed to do?  I have easily 100 passwords (and multiple login IDs), and I'd use the same password for each if I could (I know, security flaw) but just about everyone has different password composition rules.  Add to that passwords that have to be changed every 30/60/90 days, and there's no way I can keep track of all them without either saving them (which I don't do), or writing them down (which I do).

So again, what are you supposed to do?

Thanks

 




Post #114478
Posted Wednesday, May 5, 2004 7:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 67, Visits: 97

@msurasky:

I guess it's not the same security context because, the *.vbs runs in the context with which you are logged in. The SQL-Prog runs as the SQL-Server-Service winnt-account (or sql-proxy-service account if you are not logged in as SA).

//Sarus

Post #114490
Posted Wednesday, May 5, 2004 9:05 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 18, 2005 8:25 AM
Points: 37, Visits: 1

Thanks Sarus and thanks James... that's exactly why I have this difference in terms of resultsets!!!

Life is good again




Post #114510
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse