Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

VBA - Access to the remote server is denied because no login mapping exists Expand / Collapse
Author
Message
Posted Wednesday, August 14, 2013 9:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 7:48 PM
Points: 4, Visits: 23
Hello,

I had a VBA code which has been running fine for the last one year and then it suddenly stopped working.
Basically in this code I am just executing a Stored Procedure on a SQL Server Database.
Also I checked and the readonly user does exist.

connStr = "Driver={SQL Server};Server=SQL20;Database=ABC;user id=readonly;password="

Dim Cmd1 As ADODB.Command

'Setup the connection to the database
Dim connection As ADODB.connection
Set connection = New ADODB.connection
connection.ConnectionString = connStr
'connection.ConnectionTimeout = 500

connection.Open

'Open recordset
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = connection

Cmd1.CommandText = "SPCreate"

Cmd1.CommandType = adCmdStoredProc


Cmd1.Parameters("@pileNum").Value = pileNum
Cmd1.Parameters("@startDate").Value = CDate(startDt)
Cmd1.Parameters("@endDate").Value = CDate(endDate)


Dim myRecordset As ADODB.Recordset

Cmd1.CommandTimeout = 500
Set myRecordset = Cmd1.Execute() - THIS IS WHERE IT GIVES THIS ERROR (Access to the remote server is denied because no login-mapping exists)


Post #1484578
Posted Wednesday, August 14, 2013 11:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 2,078, Visits: 2,411
Please make it clear if it involves a MS Access database (because you posted this in the MS Access forum) or a SQL Server database (like you mention in the post).

If it involves SQL:
- Check for any errors in the SQL Error log
- Check if the login you use still exists on the instance
- Check if the login has the correct permissions on the database
- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report'


If it involves MS Access:
- Can you manually start MS Access, open the database and execute the command (using the user credentials you use in the code)


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1484593
Posted Thursday, August 15, 2013 2:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 26, 2013 7:48 PM
Points: 4, Visits: 23
- Check for any errors in the SQL Error log - NO ERROR
- Check if the login you use still exists on the instance - DOES EXIST
- Check if the login has the correct permissions on the database - YES
- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report' - Executed this command but it gave me only 2 usernames with UserID's.

Post #1484638
Posted Thursday, August 15, 2013 2:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 2,078, Visits: 2,411
khurmi.navpreet (8/15/2013)- Check if the login is connected to the user in the database: use {db_name};exec sp_change_users_login 'Report' - Executed this command but it gave me only 2 usernames with UserID's.


The 2 usernames with the UserID's indicate that a connection between the user and the login is lost. The connection is lost due to a mismatch between the UserID's of the Login and the corresponding User. Most probably a database restore is executed or the login is deleted and re-created.
You can use the following command for each listed username to re-connect them again:
use {db_name};exec sp_change_users_login 'Auto_fix', '{username}'



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1484646
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse