Query Multiple SQL Databases Using _SQL.au3 UDF???

[buymeapc]

I'm trying to query multiple SQL databases to see if certain users have "datareader" permissions set. The problem I'm having, however, is that the query I have below won't work with _SQL_GetTable2d() with the "USE" statement at the beginning. If I remove it, the query works fine, but I'm limited to the master database alone. I want to query all databases loaded. Is there a way I can go about this? What might I be missing?

Thanks for the help.

expandcollapse popup

#include <_sql.au3>
Dim $array[4] = [3,"db1","db2","db3"]
Local $olddb = "%"
Dim $aPerms[1][2], $sQuery
$aPerms[0][0] = $array[0]
; Tested without carriage returns
;~ $sQuery = "use "& $olddb &" select [Login Type]=case sp.type when 'u' then 'WIN' when 's' "& _
;~  "then 'SQL' when 'g' then 'GRP' end,convert(char(45),sp.name) as srvLogin, "& _
;~  "convert(char(45),sp2.name) as srvRole,convert(char(25),dbp.name) as dbUser,convert(char(25),dbp2.name) "& _
;~  "as dbRole from sys.server_principals as sp join sys.database_principals as dbp on sp.sid=dbp.sid "& _
;~  "join sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join sys.database_principals "& _
;~  "as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join "& _
;~  "sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join sys.server_principals as sp2 on "& _
;~  "srm.role_principal_id=sp2.principal_id;"
$sQuery = "use "& $olddb & @CRLF & _
"select" & @CRLF & _
"[Login Type]=" & @CRLF & _
"case sp.type" & @CRLF & _
"when 'u' then 'WIN'" & @CRLF & _
"when 's' then 'SQL'" & @CRLF & _
"when 'g' then 'GRP'" & @CRLF & _
"end," & @CRLF & _
"convert(char(45),sp.name) as srvLogin," & @CRLF & _
"convert(char(45),sp2.name) as srvRole," & @CRLF & _
"convert(char(25),dbp.name) as dbUser," & @CRLF & _
"convert(char(25),dbp2.name) as dbRole" & @CRLF & _
"from" & @CRLF & _
"sys.server_principals as sp join" & @CRLF & _
"sys.database_principals as dbp on sp.sid=dbp.sid join" & @CRLF & _
"sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id join" & @CRLF & _
"sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join" & @CRLF & _
"sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join" & @CRLF & _
"sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id"
Local $pass = "1234"
Local $server = "server"
Local $user = "user"
; Load the SQL error handler
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
$oADODB = _SQL_Startup()
_SQL_ConnectionTimeout(-1, 3); Set the SQL connection timeout to 3 seconds - default is 15. In case the server doesn't have SQL, the time to launch won't take too long this way.
If $oADODB = $SQL_ERROR Then Return "Error starting SQL ADODB connection"
$status = _SQL_Connect($oADODB, $server, "database", $user, $pass, False)
If $status = $SQL_ERROR Then
Return "Error connecting to database";Msgbox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
EndIf
;;;
For $u = 1 To $array[0]
$sQuery = StringReplace($sQuery, "use "&$olddb, "use "&$array[$u], 1, 1)
$olddb = $array[$u]
ReDim $aPerms[$u+1][2]
$aPerms[$u][0] = $array[$u]
$aPerms[$u][0] = 1
Dim $aCodingPerms, $iRows, $iColumns
ConsoleWrite([email="$sQuery&@CRLF"]$sQuery&@CRLF[/email])
If _SQL_GetTable2d(-1, $sQuery, $aCodingPerms, $iRows, $iColumns) = $SQL_OK Then
ConsoleWrite("!"[email="&@CRLF"]&@CRLF[/email])
If IsArray($aCodingPerms) Then
_ArrayDisplay($aCodingPerms)
If @OSVersion = "WIN_2003" Then
    If _ArraySearch($aCodingPerms, @ComputerName&"\ASPNET") = -1 Then
     ;Set an X
     $aPerms[$u][0] = 0
    ElseIf _ArraySearch($aCodingPerms, "NT AUTHORITY\NETWORK SERVICE") = -1 Then
     ;Set an X
     $aPerms[$u][0] = 0
    EndIf
Else
    If _ArraySearch($aCodingPerms, "NT AUTHORITY\NETWORK SERVICE") = -1 Then
     ;Set an X
     $aPerms[$u][0] = 0
    EndIf
EndIf
EndIf
Else
Msgbox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
$aPerms[$u][0] = "?"
EndIf
Next
_SQL_Close()
_ArrayDisplay($aPerms)

Edited July 10, 2012 by buymeapc

[buymeapc]

*Shameless bump*