Our RDM database is on SQL Server Express 14, on a Windows Server 2016 server. Users are using Windows 10 on a Server 2016 domain.
I can create a new user with domain credentials without a problem, but when I try to create a non-domain user using database authentication, the 'Test Server' button works OK, but get the following error when trying to connect to the database. The new user is created on the SQL server for that database.
The 'Fix SQL Login' button doesn't help, nor does making the user an administrator in RDM. Any ideas on how to fix this?
System.Data.SqlClient.SqlException (0x80131904): The SELECT permission was denied on the object 'DatabaseInfo', database 'rdm_IT', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.GetData(String sql, IDbDataParameter[] parameters)
at Devolutions.RemoteDesktopManager.Business.DataSources.DatabaseConnectionDataSource.GetDataSourceSettings()
at Devolutions.RemoteDesktopManager.Managers.ConnectionManager.c29fbd34504079a3525ca4c390d5d4074(BaseConnectionDataSource c7f28727fc7a5daa94ba08f688b2a5781)
at Devolutions.RemoteDesktopManager.Managers.ConnectionManager.ca4799bebd8b706f34eea94a778843b55(BaseConnectionDataSource c7f28727fc7a5daa94ba08f688b2a5781, Boolean cfe205b02d73750f8fd14d199343fe545)
at Devolutions.RemoteDesktopManager.Managers.ConnectionManager.LoadConnections(BaseConnectionDataSource dataSource, ConnectionEngine currentEngine)
ClientConnectionId:5580a931-2c79-495a-a3cd-3e5ab7de3bad
Error Number:229,State:5,Class:14
Hello,
with which RDM version are you getting this error?
Does it work when you create the user with the RDM User Management console?
Regards,
Min
I've attached a SQL script to list the rights assigned to all users in the database.
Can you please execute the script on your database and send us the output. Send us either all rows or the rows of the two users (yours and the affected user) for analysis.
Best regards,
Stéfane Lavergne
Show-Permissions.sql
Good question Min, it turns out I was using the only remaining RDM v14 installation in the company, the rest were on v2020.20! I updated it and deleted the user and started again, but there are still errors.
Yes I was using the RDM user management console, it gave me that SQL error.
Stefane thanks for the script, it showed some odd results so I've PM'd you the information.