SQL Server Login Issues – Can’t Access Custom Database

PHPsql serversql server 2014wamp

I have wamp installed in my system and sql server 2014, and configure so i can access sql server db through php.. with this code:

<?php 
$serverName = "MY-PC\sqlexpress"; 

$connectionInfo = array("Database"=>"master");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
 echo "Connection established.<br />";
}else{
 echo "Connection could not be established.<br />";
 die( print_r( sqlsrv_errors(), true));
}
?>

that actually works. But when i change "master" with some "customdb" its giving me this error.

Error: 18456, Severity: 14, State: 38. Login failed for user 'NT
AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified
database 'customdb'. [CLIENT: ]

Best Answer

It seems you haven't added to service account as a database user to customdb:

USE customdb;
CREATE USER [NT AUTHORITY\SYSTEM];

You will also need to grant the needed permissions on database objects to allow a minimally privileged user to access data. I suggest you do this via role membership to facilitate manageability:

USE customdb;
CREATE ROLE YourRole;
ALTER ROLE YourRole ADD MEMBER [NT AUTHORITY\SYSTEM];
GRANT SELECT ON YourTable TO YourRole;

These symptoms may also be due to the account name and SID beging different, perhaps because the database was restored/attached. Try dropping and recreating the login and user.

Related Question