SQL-DMO problem [Solved] - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL-DMO problem [Solved]

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-09-09 : 13:32:46

Doesn't anyone use SQL-DMO out there? 33 reads, 0 responses since Thursday?!?

This is the only thing I am stuck with before I close this project for the client. I cannot seem to enumerate login mappings in SQL-DMO.

The following code should work on all servers. I am implementing this in Access VBA, but any VB implementation should do.

I am wondering why oLoginMappings seems to narrow things down to only one server login even though there are 22 logins in oServer.Logins ... ?

Set oServer = New SQLDMO.SQLServer2
With oServer
  .LoginTimeout = 1 'seconds
  .QueryTimeout = 20
  .LoginSecure = True
  .Connect "ServerName"
End With
Set oLoginMappings = oServer.EnumLoginMappings
With oLoginMappings
  For iMapping = 1 To .Rows
    Debug.Print .GetColumnString(iMapping, 1)
    Debug.Print .GetColumnString(iMapping, 2)
    Debug.Print .GetColumnString(iMapping, 3)
    Debug.Print .GetColumnString(iMapping, 4)
  Next iMapping
End With

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-13 : 13:26:57

I seem to be one of the many people out here who don't use sql-dmo. Since your 2nd "cry for help", I see there are still no resonses so I just want to say, "You're not alone" ...well, nevermind, I guess you are. Good luck!

Now for something with a very slight chance of being constructive:
Seems to me that login mappings would be specific to a database as opposed to logins which is server based. I don't see anything in your code (above) that specifies what database you want mappings for. I guess it's looking at the mappings for your connection account's default database? Should you expect to see the same thing as sp_helpuser?

Be One with the Optimizer
TG

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-09-13 : 14:06:29

A reply at last!

was getting real shiny!
In Enterprise Manager, when I double-click on a login (<server>|Security|Logins|<loginName>), then choose the Database Access tab, I see in the top list a checkmark next to each database that login is permitted to access. THAT is the list I want from SQL-DMO.
"Should you expect to see the same thing as sp_helpuser?" - No, I am expecting to see something like the second result set of SP_HelpLogins.

~ Shaun Merrill
Seattle, WA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-13 : 14:25:03

well, again, sorry, I'm not a sql-dmo guy. One other thing: According to BOL for EnumLoginMappings, it returns a query results object with multiple result sets. Does your
"With oLoginMappings For iMapping = 1 To .Rows" deal with multiple result sets? I know for a C# datareader you need a ReadNext after all your Reads to get the next result set.

Be One with the Optimizer
TG

SMerrill
Posting Yak Master

206 Posts

Posted - 2005-09-13 : 14:57:04

Hey, I think you've observed exactly what I was lacking.
Thanks to you, I have revised my code as follows:

If oServer.IssysAdmin Then
    Set oLoginMappings = oServer.EnumLoginMappings
    With oLoginMappings
        For iResultSet = 1 To .ResultSets
            .CurrentResultSet = iResultSet
            For iMapping = 1 To .Rows
                Debug.Print .GetColumnString(iMapping, 1)
                Debug.Print .GetColumnString(iMapping, 2)
                Debug.Print .GetColumnString(iMapping, 3)
                Debug.Print .GetColumnString(iMapping, 4)
            Next iMapping
        Next iResultSet
    End With
End If

Good job.
~ Shaun Merrill
Seattle, WA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources