You’re offline. This is a read only version of the page.
405
GP2010
SQL 2008 R2
In our DEV or TEST environment, it is often necessary to pull in a fresh copy of the LIVE data. Many times (nay, MOST times) this causes the user logins to fail. Simple enough to just delete the user and re-add them, right? Yes, unless you get the "Deleting the login failed for an unknown reason. Contact your SQL Server Administrator for assistance" error. There is a work-a-round for this in KB 943027 (support.microsoft.com/.../943027) but I was still having some issues even after following those instructions.
When executing the statement DELETE DYNAMICS..SY10000 WHERE USERID = 'User_name', I get the following message:
Msg 4405, Level 16, State 1, Line 1
View or function 'DYNAMICS..SY10000' is not updatable because the modification affects multiple base tables.
However, if I run the below, then SELECT * FROM SY10000 WHERE USERID = 'User_name', no results are displayed (which is good).
-- Ctrl+H to find and replace the 6 occurances of the user's name
DECLARE @USER as varchar(30)
SET @USER = 'User_name'
DELETE COMPANY_1..SY01401 WHERE USERID = @USER
DELETE COMPANY_2..SY01401 WHERE USERID = @USER
DELETE COMPANY_3..SY01401 WHERE USERID = @USER
DELETE DYNAMICS..ACTIVITY WHERE USERID = @USER
DELETE DYNAMICS..SY10500 WHERE USERID = @USER
DELETE DYNAMICS..SY08000 WHERE USERID = @USER
DELETE DYNAMICS..SY60100 WHERE USERID = @USER
DELETE DYNAMICS..SY01600 WHERE USERID = @USER
DELETE DYNAMICS..SY01400 WHERE USERID = @USER
DELETE DYNAMICS..SY01403 WHERE USERID = @USER
DELETE DYNAMICS..SY60100 WHERE USERID = @USER
DELETE DYNAMICS..SY10550 WHERE USERID = @USER
USE DYNAMICS
DROP USER User_name
GO
USE COMPANY_1
DROP USER User_name
GO
USE COMPANY_2
DROP USER User_name
GO
USE COMPANY_3
DROP USER User_name
GO
/*
Only run this if you're still having an issue adding user back to GP as this will
remove the SQL login and affect this user's access to all databases on this instance.
DROP LOGIN User_name
GO
*/
I hope someone finds this helpful and please, VIP's, if you see a glaring issue with any of this, feel free to critique.
*This post is locked for comments
Nice post Ian.
Hello There;
I can explain why you have the issues and a fix.
Please not that for GP there are 3 logins (sort of) to think about.
1 - the GP user account, the one found within the GP database
2 - the SQL Account - the one found under security - logins in SQL Mgmt Studio
3 - the Database User Account - the one found under security users within the actual database in SQL Mgmt Studio
When you create a user in GP, it creates a SQL Login, and a database user in the DYNAMICS database
The differetiation is important - 1 exists in the MASTER database tables and tells SQL that you have access to the SQL instance
the other exists in the database itself
When you add users to companies via GP, then a subsequent database user account is added to the company database
These database users are 'connected' to the SQL Login; but note they are not one in the same.
When you backup a database and restore it between SQL instances either on the same server or different server, OR you copy a LIVE company over a TEST company within the same instance, the database user follows the database; however the SQL Login does not unless you backup and restore the MASTER database also.
So because of this you have many scenarios for disconnects between users in the DYNAMICS database tables (SY01400 etc) and SQL Logins on the SQL server\instance and DATABASE users
Typically the FASTEST way to fix this;
If you look at SQL and the LOGIN exists (at the server level, Security - Logins) and the User also exists in the DYNAMICS database then you can normally just go to the COMPANY database and delete the account via SQL Mgmt Studio
Back in GP you can now add the user to the company
This works if GP says the user doesnt have access to the company but SQL shows the user as present in the database
Now, if GP shows the user as HAVING access to a company database; but the user cannot login - this is usually because the Database User account is missing in SQL, but within the DYNAMICS security tables, the user is present.
In this case if you open the User Access Screen, start at the top and arrow down past all of the users GP will automatically SYNC the accounts in GP and grant ACCESS in the right company databases
NOTE that this will NOT work if the user is MISSING from the DYNAMICS database in SQL (workaround - simply pop in SQL and grant the account access to DYNAMICS manually and add to DYNGRP)
If users are missing from SQL and you want to sync up the environments you can use a simple SQL script
Let me know if this helps at all or if you need a better explaination or more information and I will do my best to mae it all make sense for you.
(In SQL 2012 with contained database the MASTER database/User Database doesnt exist - but because GP users a multi database model this will not ever work :-( )
Cheers
Ian
I find a lot of the time that the users that will be in your test environment are a subset of who is in your production environment, so why bother moving all your production users over if you don't need them. When I refresh from production, I typically will not refresh the Dynamics database unless I absolutely have to. In my refresh script I have to do a bunch of clean up stuff anyway (clensing email addresses, disabling auditing triggers, update msmq paths for econnect, etc) so I have put in some code to create my dev users if they don't already exist and made updates to the dynamics tables that I need to sync up between the new prod company databases and the existing dynamics db on my test box.
Hey Jody,
It's probably more of a situation where the GP user's SQL id doesn't exist in your test environment. Sure they are there under security for the databases, but not the instance of SQL hosting your test environment. If you are going to use data from prod(dynamics db + a company db) and restore in test, may I suggest copying the sql logins from prod as well, and import them into your test sql instance. KB 878449 has a file you can copy the sql logins from prod. Run the script in prod, and the result is a script you run in test that inserts those same logins into the test environment. That way the userid(and more importanly) and sql sid are the same in both instances. GP likes that sid, a lot! you'll have to reset the passwords in test.
hope you or someone else finds this useful.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (