Website using MSSQL is inaccessible: SqlException (0x80131904): The SELECT permission was denied on the object

Symptoms

• Website using Microsoft SQL Server database is inaccessible with the following error:

  SqlException (0x80131904): The SELECT permission was denied on the object 'UserName', database 'DBName', schema 'dbo'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442686
   ...
   TgAdmin.UpdateSmart..ctor() +137
   TgAdmin.SmartUploadTask..ctor() +26
   TgAdmin.Global.Application_Start(Object sender, EventArgs e) +44

Cause

The “Select” permissions are missing for the database user “UserName”.

Resolution

1. Connect to the server using RDP;
2. Execute the following in “Microsoft SQL Server Management Studio” in “Query Editor”, if it is installed. Otherwise, sqlcmd or OSQL command prompt utilities can be used (Depending on MS SQL Server version).

   Note: The following example uses the default Microsoft SQL Server 2016 files location and instance name SQL2016. DBName and UserName must be replaced with actual ones.

   “%ProgramFiles%Microsoft SQL ServerClient SDKODBC130ToolsBinnsqlcmd.exe” -E -S .SQL2016

   USE [DBName]
   GO
   EXEC sp_addrolemember N’db_datareader’, N’UserName’
   GO

   OR

   GRANT SELECT ON dbo::DBName TO UserName;
   GO