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
- Connect to the server using RDP;
- 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’
GOOR
GRANT SELECT ON dbo::DBName TO UserName;
GO