Skip to content


Login failed for user

johnellis asked 10 years ago
A security issue has occurred at a GP 2010 client of ours who uses SmartConnect 2012.  All of the GP and SmartConnect users access those apps via Terminal Server.
Up until now, two of their GP users had Admin access to SmartConnect.  Then, for whatever reason, their DBA ran the script below.  Now, these two users get the error "Login failed for user", upon trying to run a map.

Specifically, when one of these two users tries running the map, the error message references the other user–not her.  And, the error message doesn't even reference the domain name as part of the user name.
The client did try to fix this by deleting these users from SmartConnect, re-adding them, and granting SmartConnect Admin access once again.  But, no luck.
The AD logins of these users, in SQL Management Studio, show that indeed they have DYNGRP and Public access to the GP databases as well as sysadmin access.  And, this issue is only occurring for these two users.
Would running SmartConnect System Maintenance fix this?  If not, then what is the remedy?
Thanks!  Much appreciated!

Here is that script:

To add Windows AD users to the GP databases use the script below.
Replace domainusername with the login details for each user
IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'domainusername')
CREATE USER [domainusername] FOR LOGIN [domainusername] WITH DEFAULT_SCHEMA=[dbo]
EXEC sys.sp_addrolemember 'DYNGRP',[domainusername]
CREATE USER [domainusername] FOR LOGIN [domainusername] WITH DEFAULT_SCHEMA=[dbo]
EXEC sys.sp_addrolemember 'DYNGRP',[domainusername]
duplicate the lines below for each GP company database the user should be able to access.
CREATE USER [domainusername] FOR LOGIN [domainusername] WITH DEFAULT_SCHEMA=[dbo]
EXEC sys.sp_addrolemember 'DYNGRP',[domainusername]
where TWO is the Company ID
Alternatively an AD user group may be added to MSSQL, the appropriate permissions added, then the AD users added to the AD user group.
From SmartConnect 2011 SP2 administrators will be able to define a single MSSQL user which SmartConnect should use to connect to the Dynamics GP Company databases.
johnellis replied 10 years ago

Disregard…..the solution was to look into the SmartConnect Setup and specifically the GP Connector's properties.  This user was in this window instead of "sa", for some reason.  Once I reentered sa's credentials, the issue went away.

Martin replied 10 years ago

John – I love that you are now answering your own questions.  Great Work.

If you would like to submit an answer or comment, please sign in to the eOne portal.