sql server - Show all database in sqlsrv by php

370

I want to display all databases in microsoft sql srv for admin panel, but i have problem with get value. I'm try with query:

EXEC sp_databases

This query is executed successfully in sql srv, but when i'm try make this same by PHP, im not see value, no any errors or warnings, return null

My PHP Code:

    <?php
$serverName = $_POST['hostname'];
$uid = $_POST['username'];
$pwd = $_POST['password'];
$connectionInfo = array( "UID"=>$uid,                              
                         "PWD"=>$pwd); 
$connsrv = sqlsrv_connect( $serverName, $connectionInfo);   

if($connsrv == TRUE ){

    echo "connected";

    $tsql = "EXEC sp_databases";
    $stmt = sqlsrv_query( $connsrv, $tsql);      
    while( $row = sqlsrv_fetch_array($stmt)){
        echo $row['DATABASE_NAME'];
    }

}else{
    echo "no connect";
}

?>

I don't know why I not see any result - any suggestions ? :(

391

Answer

Solution:

One possible explanation for this unexpected behaviour is thatCREATE DATABASE orALTER ANY DATABASE orVIEW ANY DEFINITION permissions are requied to run thesp_databases stored procedure (I can reproduce this with a server login, which has onlypublic role). Set the needed permissions for the connection user, or as another option, try to use thesys.databases system view.

<?php
// Connectuion
$serverName = $_POST['hostname'];
$uid = $_POST['username'];
$pwd = $_POST['password'];
$connectionInfo = array("UID" => $uid, "PWD" => $pwd); 
$connsrv = sqlsrv_connect($serverName, $connectionInfo);   
if ($connsrv === false){
    echo "Not connected";
    exit;
}

// sp_databases
echo "Connected. EXEC sp_databases: "."<br>";
$tsql = "EXEC sp_databases";
$stmt = sqlsrv_query( $connsrv, $tsql);      
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
while( $row = sqlsrv_fetch_array($stmt)){
    echo $row['DATABASE_NAME']."<br>";
}
sqlsrv_free_stmt($stmt);

// sys.databases
echo "Connected. SELECT * FROM sys.databases: "."<br>";
$tsql = "SELECT * FROM sys.databases";
$stmt = sqlsrv_query( $connsrv, $tsql);      
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
while( $row = sqlsrv_fetch_array($stmt)){
    echo $row['name']."<br>";
}
sqlsrv_free_stmt($stmt);

// End
sqlsrv_close($connsrv);
?>

People are also looking for solutions to the problem: php - How to resolve this Laravel issue - The "/tmp/phpY14gRo" file does not exist or is not readable?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question