GoldMine Web (GMWEB) - When trying to log into GMWEB, getting error message Error, Run SQLCollation statements error., the GMConnect.Log file shows:  Run add dependency sql statemens error., System.Data.SqlClient.SqlException (0x80131904): CREATE PROCEDU

Version 1

    Details

    GoldMine Web (GMWEB) - When trying to log into GMWEB, getting error message Error, Run SQLCollation statements error., the GMConnect.Log file shows:  Run add dependency sql statemens error., System.Data.SqlClient.SqlException (0x80131904): CREATE PROCEDURE permission denied in database 'xyz'.


    [STEPS TO PREPARE]

    It is recommended to perform these steps in a test environment and/or a demo/test database
    1. in SQL Server Management Studio
    2. Browse to Security
    3. Right Click on Logins >> New Login
    4. Provide the new login data with SQL Authentication, for example Login name testgm
    5. Switch on the left pane to User Mapping
    6. Check the GoldMine database for the test scenario
    7. check the role memberships for the GoldMine database as public, db_datareader, db_datawriter



    8. OK

    9. In GoldMine log in as a user with master rights
    10. Tools >> Databases >> Alias Manager
    11. Edit the current used database alias >> Button Edit Alias
    12. Adjust the used SQL login information to the new created SQL Login from above


    13. Button Test Connection (which should result in Connected to Database successfully) 
    14. Login again into GoldMine
    15. Verify via Help >> About GoldMine >> Button System >> Logged User entry should display now as the SQL Login: testgm)
    16. OK

     
     


    [STEPS TO REPRODUCE]
    1. Log into GoldMine Web
    >> RESULT: Error, Run SQLCollation statements error.



    2. Reviewing the related GMConnect.Log (by default in the GoldMine Connect installation folder\Logs folder) shows the error

    2015-11-05 19:00:47,705 - [ERROR] Thread id[9] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Run add dependency sql statemens error.,
    System.Data.SqlClient.SqlException (0x80131904): CREATE PROCEDURE permission denied in database 'GoldMine201410440USDemo2'.

    Server stack trace:
       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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at SqlDependencyProcessDispatcher.SqlConnectionContainer.CreateQueueAndService(Boolean restart)
       at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults)
       at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults)
       at SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart)
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at SqlDependencyProcessDispatcher.StartWithDefault(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& service, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart)
       at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)
       at GoldMine.Models.GmSqlDependency.SqlDependencyRepository.Initialization(String connection_str)
       at GoldMine.Models.GmSqlDependency.SqlDependencyRepository.AddDependency(String sSQL, OnDependencyChangeDelegate OnDependencyChange, String connection_str)
    ClientConnectionId:9761171f-89a1-452b-9736-e93871ec7120
    Error Number:262,State:18,Class:14
    2015-11-05 19:00:47,708 - [ERROR] Thread id[9] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Run add dependency sql statemens error.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)
       at GoldMine.Models.GmSqlDependency.SqlDependencyRepository.AddDependency(String sSQL, OnDependencyChangeDelegate OnDependencyChange, String connection_str)
       at GoldMine.Models.GmSqlDependency.OnDependencyChangeHandler.Subscribe()
       at GoldMine.Models.GoldMineAPI.GoldMineAPIEntry.DatabaseRebuildDependency.SetSqlDependency()
    2015-11-05 19:00:47,708 - [ERROR] Thread id[9] - GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.,
    GoldMine.Models.ApplicationExceptions.ClientInformationException: Error, Run SQLcollation statements error.
       at GoldMine.Models.Log.GMLoggerFactory.GMLogger.ErrorClientAndServer(Object customMessage, Exception e)
       at GoldMine.Models.GoldMineAPI.GoldMineAPIEntry.DatabaseRebuildDependency.SetSqlDependency()
       at GoldMine.Models.GoldMineAPI.GoldMineAPIEntry.Load(String userName, String password, Boolean isWeb)


    Resolution

    [RESOLUTION]
    - The used SQL user requires sufficient rights, means the SQL login needs to have db-owner rights on the GoldMine database. While this article rather refers to GoldMine Connect, this is also recommended for the GoldMine application itself.
    - Microsoft SQL DBAs may consider to provide explicit further rights for the SQL login instead of providing db-owner rights, GoldMine Technical Support will not assist in identifying specific necessary rights and also may require while investigating a reported behavior to provide db-owner rights to the used SQL login or even using temporarily the sa user account for testing. 



    - After providing the SQL user sufficient rights, the user can log into GoldMine Web