The SELECT permission was denied on the object '', database '', schema 'dbo'. using DBML

Question

I have a site that was working fine until this morning. Now I am getting a

'The SELECT permission was denied on the object '', database '', schema 'dbo'.

error. The thing is, the method is part of some DBML code and I can;t even find the connection string.

    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="RAW.GetCustomers")]
    public ISingleResult<GetCustomersResult> GetCustomers()
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
        return ((ISingleResult<GetCustomersResult>)(result.ReturnValue));
    }

This is the generated function call that is causing the error. I have to assume that there was some permission change in the database, or the login is no longer valid, but I can't find any connection information in this code. There is nothing in the web.config either. Where do I find the connection information in all of this? All I have is a diagram with a list of functions that get called. its the GetCustomers() function (see above) that is causing the error.

Here is the web.Config. It shows the RawParts connection sting, but not the one to the database that is causing the problem (dynamics is its name)

    <?xml version="1.0" encoding="UTF-8"?>

<configuration>
  <connectionStrings>
    <add name="MiscReportTablesConnectionString" connectionString="Data Source=pmfcgrb12;Initial Catalog=MiscReportTables;Persist Security Info=True;User ID=USERNAME;Password=PASSWORD"
      providerName="System.Data.SqlClient" />
  </connectionStrings>

  <system.web>
    <compilation debug="true" targetFramework="4.0" />

    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>

    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/" />
      </providers>
    </profile>

    <roleManager enabled="false">
      <providers>
        <clear/>
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" />
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
      </providers>
    </roleManager>

    <customErrors mode="Off"/>

  </system.web>

  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true" />
        <defaultDocument>
            <files>
                <clear/>
                <add value="default.aspx"/>
            </files>
        </defaultDocument>
  </system.webServer>
</configuration>

Is this a production system or can you access source and attach the debugger? If the former, I would start by checking with your infrastructure team to see if you have any interruptions or outages. If you can use the debugger, it shouldn't be difficult to trap where the method in question is getting its connection string from. — mjw
This is LinqToSql - there should be class that inherits from System.Data.Linq.DataContext (you can pass connection string in constructor of this class). Default constructor than takes connection string from file Settings.settings. — Ondrej Svejdar
Sounds to me like someone has messed with the permissions on the database side - maybe ask around to see if anyone's changed anything — codeulike

Russell Russell · Accepted Answer · 2018-10-22T00:50:19

It looks like you are using RAW schema for your function. Ensure your login user has access to the schema, and check the contents of the function to see if it is making use of any objects under the dbo schema, which the user would not have permission to use.

Try running the function as the user in Management Studio as the user to isolate a user permission issue vs. a connection string app issue.

The SELECT permission was denied on the object '', database '', schema 'dbo'. using DBML

1 Answers