I have always been intrigued with the approach taken for the Dynamics GP security model. It creates an actual sql user for each user that is added to the GP system. This is great for debugging or tracking what a user is doing and when errors come up. You can use SQL Profiler to isolate the one users SQL calls and debug a lot. I am not sure exactly when this new element of the security was added and I would love to know what the actual purpose of it is, but now each user not only has an actual SQL user login but a schema was created for each user that not only is named after the user but the user owns the schema. This causes problems when you try to change the companies a user has access to or when you are trying to remove users that are no longer with the company.
As I tried to remove old users I was met with an error message that is quite generic…
“Deleting the login failed for an unknown reason. Contract your SQL Server administrator for assistance.” or if you try to change the users access you will be confronted with this similarly generic message “This user could not be removed from one of more databases”.
Yes I have to say thank you for the helpful message. And if I were the dba of the GP system I would have just much of an idea where to look as the user that encountered the message. In this case running profiler is not help either. You would not want to try to delete the user from the database level because you will likely never get them out of the system.
Ok here is the answer. KB Article ID: 919723 here is the link:
This will run you through the steps to take care of removing or changing user access. Essentially you end up removing the users ownership of the schemas that were created. The article gives you a handy little script, but if for some reason you can not run the script you can manually change the ownership of the schemas in each database as stated in step 2. They will be in the base Dynamics Database and any company that was in existence when the users security was initiated in at least 2010. Because my company used advanced security in 9 and then moved straight to 2010 I can not speak for 10. Also we did not allow GP to “upgrade” the security of the users. We manually recreated the security for the users to use one “Alternate/Modified Forms and Reports ID”. We did not want one ID created for each user, the maintenance on that would be a nightmare if you ever added a new form or modified an existing one.
But if you receive the error messages above when trying to change access or delete users these are the steps to resolve the problem.
I scratch my head at this one, mainly because since the upgrade I have created a new company’s and the users did not have schemas created in that new company.