ODBC SELECT permission denied

Question

I want a basic "SELECT * FROM Kunde" printed out on my PHP website.

Got the following error:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 229 [code] => 229 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The SELECT permission was denied on the object 'Kunde', database 'Test_DB', schema 'dbo'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The SELECT permission was denied on the object 'Kunde', database 'Test_DB', schema 'dbo'. ) )

System: Win2016, SQL Server 2017, PHP 7.4.3, sqlsrv module and odbc 17 installed, connection to sql server succesfull, sql query (SELECT * FROM Kunde) works in SSMS, I granted myself all posible permissions on all possible SQL Objects...

Edit1:

<?php


// Create connection
$servername = "servername\SQLEXPRESS";
$dbname = "Test_DB";
$connectionInfo = array( "Database"=>"Test_DB");

$conn = sqlsrv_connect($servername, $connectionInfo);

if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

if( $client_info = sqlsrv_client_info( $conn)) {
    foreach( $client_info as $key => $value) {
        echo $key.": ".$value."<br />";
    }
}
echo "<br>";

$server_info = sqlsrv_server_info( $conn);
if( $server_info )
{
    foreach( $server_info as $key => $value) {
       echo $key.": ".$value."<br />";
    }
} else {
      die( print_r( sqlsrv_errors(), true));
}

echo "<br>";

//Show DB

$sql = "SELECT * FROM dbo.Kunde";
$stmt = sqlsrv_query($conn, $sql);

if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}


sqlsrv_close( $conn );
?>

How do you conect to database - with sql or windows authentication? And, if possible, post your PHP code. — Zhorov

Zhorov Zhorov · Accepted Answer · 2020-03-23T13:18:27

You are trying to connect to SQL Server using windows authentication. In this situation, the Web server's process identity or thread identity (if the Web server is using impersonation) is used to connect to the server. When you are executing this statement in a client tool (SQL Server Management Studio) you are using an end-user's identity.

You need to do the following:

grant permissions to Web server's process identity or
use sql authentication to connect to the SQL Server

With this simple script, you can get the name of the user, which tries to connect to the server:

<?php
$server = 'servername\SQLEXPRESS';
$cinfo = array(
    "Database"=>'Test_DB'
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}
$sql = 
    "SELECT 'SUSER_SNAME' AS [NAME], CONVERT(nvarchar(128), SUSER_SNAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'SUSER_NAME' AS [NAME], CONVERT(nvarchar(128), SUSER_NAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'USER_NAME' AS [NAME], CONVERT(nvarchar(128), USER_NAME()) AS [VALUE]".
    "UNION ALL ".
    "SELECT 'USER_ID' AS [NAME], CONVERT(nvarchar(128), USER_ID()) AS [VALUE]";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo $row['NAME'].": ".$row['VALUE']."</br>";
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>

ODBC SELECT permission denied

1 Answers