buymeapc Posted July 10, 2012 Share Posted July 10, 2012 (edited) 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 Link to comment Share on other sites More sharing options...
buymeapc Posted July 13, 2012 Author Share Posted July 13, 2012 *Shameless bump* Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now