SQL-Dynamics V10 Users

I have some messy stuff going on with Dynamics and SQL users.  I have users 
that are users in the databases, but not in Dynamics, and users in Dynamics 
that are not in SQL.

Is there a script or anything to run to straighten this out?

Right now it is manual process where I have to go user by user,  check in 
dynamics and which companies they use, then go to SQL and check each 
database...

Any suggestions?
-- 
Doug
0
Doug4515 (774)
5/7/2009 5:54:06 PM
greatplains 29623 articles. 6 followers. Follow

4 Replies
423 Views

Similar Articles

[PageSpeed] 25

Why can't you do this through the user interface?  Did you resore a backup of 
Dynamics of something?  As far as I know, the only way to create a GP login 
is to do it through the Add Users through security.  Are you getting 
inconsistent results when you try to grant access to companies within GP?  
I'm also pretty sure that a GP login does not actually grant access as a 
"server" login (for SQL Mgt Studio for example).

When you go to User Access in GP, you see a check box next to a company that 
means access.  Are you saying that those check boxes do not represent what 
you see under security to the database in SQL?  If a user have a checkbox in 
Company A, are you saying they still can't log into that company in GP?

"Doug" wrote:

> I have some messy stuff going on with Dynamics and SQL users.  I have users 
> that are users in the databases, but not in Dynamics, and users in Dynamics 
> that are not in SQL.
> 
> Is there a script or anything to run to straighten this out?
> 
> Right now it is manual process where I have to go user by user,  check in 
> dynamics and which companies they use, then go to SQL and check each 
> database...
> 
> Any suggestions?
> -- 
> Doug
0
KevinD (96)
5/7/2009 6:05:02 PM
Doug

I'm not sure if you can create Users outside GP in order them to be
Users of GP. Anyways, I'll wait for any possible alternatives from
other fellow members on this

Thanks
Janakiram M.P.
MCP-GP

On May 7, 8:54=A0pm, Doug <D...@discussions.microsoft.com> wrote:
> I have some messy stuff going on with Dynamics and SQL users. =A0I have u=
sers
> that are users in the databases, but not in Dynamics, and users in Dynami=
cs
> that are not in SQL.
>
> Is there a script or anything to run to straighten this out?
>
> Right now it is manual process where I have to go user by user, =A0check =
in
> dynamics and which companies they use, then go to SQL and check each
> database...
>
> Any suggestions?
> --
> Doug

0
janakirammp (688)
5/7/2009 6:11:49 PM
Doug,

Sorry you have such a mess but you are on the correct track to fix it.  A 
few pointers.

1.  You cannot create a user in MS-SQL and attach that user to a GP user.  
GP encrypts the password before passing it to SQL so any PW you enter in SQL 
would not match the encrypted one in GP.

2.  For each user in GP, go to the User Access window and check all dbs.  
You might get some error messages and need to check/uncheck/then check again 
to clear the error.  Once all are checked, uncheck the companies the user 
should not have access to.  This will clean up the user lists in each company 
DB in SQL.

3.  Take a complete list of users from GP and go to SQL.  Add to your list 
any SQL users from other applications.  Remove from the GP databases 
(Dynamics and each company) any user not on the GP list.  Remove from SQL any 
user that should not be in SQL for any reason (not on your GP list and not 
from any other application you might have running.)

As you might expect 

               MAKE A BACKUP FIRST!!!!!!!!!!!!!!!


-- 
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at 
http://www.AccoladePublications.com



"Doug" wrote:

> I have some messy stuff going on with Dynamics and SQL users.  I have users 
> that are users in the databases, but not in Dynamics, and users in Dynamics 
> that are not in SQL.
> 
> Is there a script or anything to run to straighten this out?
> 
> Right now it is manual process where I have to go user by user,  check in 
> dynamics and which companies they use, then go to SQL and check each 
> database...
> 
> Any suggestions?
> -- 
> Doug
0
info4071 (3004)
5/7/2009 6:48:03 PM
Doug,

In fact going from Dynamics to SQL or viceversa is not complex. If you have 
a good amount of users I can see how going into the user access window can be 
tedious.

For users in Dynamics not in SQL, and assuming the SQL Server logins exist, 
you can use the following query:

USE DYNAMICS
GO

DECLARE @USERID CHAR(30), @CMPANY CHAR(5)
DECLARE @TSQLSTMT VARCHAR(MAX)

DECLARE c_access CURSOR FOR 
	SELECT A.USERID, B.INTERID FROM SY60100 A 
	  INNER JOIN SY01500 B ON (A.CMPANYID = B.CMPANYID)
	WHERE A.USERID NOT IN ('DYNSA', 'sa')

OPEN c_access
FETCH NEXT FROM c_access INTO @USERID, @CMPANY

WHILE @@FETCH_STATUS = 0 
BEGIN
	SET @TSQLSTMT = 'USE ' + RTRIM(@CMPANY) + '; IF  EXISTS (SELECT * FROM 
sys.database_principals WHERE name = N''' 
		+ RTRIM(@USERID) + ''') DROP USER ' + RTRIM(@USERID) + '; CREATE USER ' 
		+ RTRIM(@USERID) + ' FOR LOGIN ' + RTRIM(@USERID) + ' WITH 
DEFAULT_SCHEMA=[dbo]; EXEC sp_addrolemember ''DYNGRP'', ''' + RTRIM(@USERID) 
+ ''''
	
	EXEC(@TSQLSTMT)		
	FETCH NEXT FROM c_access INTO @USERID, @CMPANY
END

CLOSE c_access
DEALLOCATE c_access

If the SQL Server logins do not exist, changing the above code shouldn't be 
difficult. Going from SQL to Dynamics is also very simple and can be done 
with a similar approach as the one above. You will have to capture the each 
database login then add to the user master table (SY01400). If the user 
passwords are not encrypted on SQL Server, GP will force a password change 
upon the user login. 

Beware that if you have other applications using the unencrypted SQL Server 
logins, once you add them to GP, the users will not be able to access those 
applications.

For more information about GP password management, take a look at the 
following article:

http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/02/why-does-microsoft-dynamics-gp-encrypt-passwords.aspxBest regards,

Best regards,

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com


"Doug" wrote:

> I have some messy stuff going on with Dynamics and SQL users.  I have users 
> that are users in the databases, but not in Dynamics, and users in Dynamics 
> that are not in SQL.
> 
> Is there a script or anything to run to straighten this out?
> 
> Right now it is manual process where I have to go user by user,  check in 
> dynamics and which companies they use, then go to SQL and check each 
> database...
> 
> Any suggestions?
> -- 
> Doug
0
MarianoGomez (3440)
5/7/2009 8:04:06 PM
Reply:

Similar Artilces:

MFC and User Defined objects
Greetings, I am trying to PostMessage from a user defined generic class/object in MFC Dialog based application to Dialog/main window. I don't know how to do that. I am utilizing worker threads. A thread instantiates an object(from user defined generic class) and utilizes newly created object. What user defined object code does is actually launch a process, for sake for argument lets say, Windows Notepad.exe using CreateProcess. As long as that process is running, I want to PostMessage that updates a progress bar on Main GUI dialog. I can do it if I embed the code right into my appli...

Error accessign SQL data
When I run a modified report in one company I get the error "Error accessign SQL data" but I am able to runt he same report in all other companies. I have run the Grant Script and I have also verified that the DYNSA is the DB owner. Any other ideas? Thanks in advance. I've had this happen to me and here's what I checked to find the problem: 1. Are you trying to run a modified report? If yes, try the unmodified version. 2. Since security can be set by company, check the security for the user and company. 3. Check the workstations to make sure they are all pointing ...

Smartlist Builder
I have played around with several security settings but my user still cannot see the new reports I built in Smartlist Builder. I understood the user does not have to have it installed to see it in the regular Smartlist. The tasks I have assigned are as follows: Category: Other Product: Smartlist Type: Smartlist Object Series: Smartlist Objects I checked off my new reports. Also added: Category: Other Product: Smartlist Builder Type: Smartlist Builder Permissions Series: Smartlist Builder I checked off "View Smartlists with SQL Tables SmartList Bui...

GP 10 w/Terminal Server and SQL Server 2008 R2
We are a small office using Dynamics GP 10 by way of Terminal Server and SQL Server 2008 R2. We have experienced some weird problems. We have to pause between each request (to give it time to hit TS and return?) So we log into TS using Remote Desktop, Click to open GP (and wait) then type in user name and password (and wait). Select our company from the drop down (and wait). About 1 out of every ten times we will get our company and begin working. The other 9 times we get a message that GP is not responding and must close. We are wondering if the delay and GP not responding could be rela...

link sent to user does not work
An e-mail with a link to a file on our file server does not work. The sender (a domain admin) pasted the address into the e-mail (ex. <\\server\share\file>) but the recipient was not able to click on it. The recipient is a domain admin with full rights to the folder where the file is stored. I had the sender resend the email to me and I was able to click on the link and open the file. Any help would be appreciated. Let me preface that I have just joined this group, and am not near as knowledgable about this stuff as others, but like taking stabs. Is the recipient opening his...

Unable to create new user mailbox. event id 9562
Trying to setup a new users and I can create the account just fine but no mailbox is created. When I look in the event log I see ID-9562 and that talks about the RUS. All teh info points to a rebuild or RUS which i did and still no luck with the mailbox. I tried to delete the users and re-create him with the same luck. I also tried to create a different user and still can't create a mailbox. All the KB articles point to a rebuild and that doesn't get it done. Anyone have any thoughts on this. This is pretty frustrating. Any help or KB articles would be greatly appreciate...

Copying The Windows User Account
Am I able make a copy of the complete Windows user account; so that when I do a clean install I can restore the account to its previous state? Is creating the exact user name and account type sufficient, or is there other information needed? How do I do this, I am running XP Home Edition on a 32 bit machine. On Sat, 28 Aug 2010 11:24:03 -0700, paul <paul@discussions.microsoft.com> wrote: >Am I able make a copy of the complete Windows user account; so that when I do >a clean install I can restore the account to its previous state? Is creating >the exact user nam...

User Defined Fields--Employee Maintenance Screen PR & HR
In looking through the table/field descriptions I do not find the additional user defined field names associated with the PR/HR modules. When I tried to run a smartlist and attach the fields they do not appear in the window as available fields/columns. I have modified the labels and need to run reports on those specific user defined fields. Has anyone created such a report? If not, where can I locate the field names? I am a new user to this system and any assistance someone could provide would be helpful. Hi, Since there are several places with user defined fields availab...

SQL Server Agent?
Hi, someone here was playing with the SQL Server Service Manager application (in the tray below right) and now I am confused. Should the SQL Server Agent be on or off? How about Distributed Transaction Coordinator - on or off (or play/stop?)? Finally, if I wanted to see the actual database and get in there and edit fields in raw SQL format...is there any way to do that with the SQL server app installed with POS or is there some app missing that would allow me to do that? As long as the server service is running then that should be all you need. I'd advise against editing the...

What type of tech user are you?
We pose this question in our blog with a goal of better defining how many people fall into our notquitegeek category. There are 10 categories, look to th blog for more info and please post your comments. www.notquitegeeks.blogspot.com and some freebies at www.notquitegeeks.com enjoy ...

User Entity in isv.config?
Anybody know if it is possible to create a User entity tag in isv.config so that I can add a tab to the User's detail screen? It's unsupported and undocumented... but yes you can. Under the "Entities" node, simply add a "<systemuser>" tag and follow the format used by the other entities. This should work for all entities that are built on the Customizable Form Framework (basically most but Activities). Aaron Elder Director, Software Development Invoke Systems Solid technology. Sound thinking. http://www.invokesystems.com "AW" <AW@discus...

Missing Microsoft Dynamics GP Drop-Down button
I just created a new GP user. When I log into GP as this new user, the Microsoft Dynamics GP button that's normally in the upper right-hand corner is missing. Any ideas on what I can do? What version of GP? What specific button are you referring to (name) or what does it do? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Elaine" wrote: > I just created a new GP user. When I log into GP as this new user, the >...

A folder Deleted or Disappeared from User Inbox
Hi all, One of my User has issue about one of the folder being accidently deleted or disaapeared from his Mailbox while doing ActiveSync on his PDA. Is there a way I can reterive that folder. Things I cannot do. Do a restore on his mailbox as the backups ran 20 days ago..:( I dont have mailbox Recovery configured yet. Any good way or ideas to retreive that folder. A- On 28 Feb 2007 14:36:08 -0800, "Barundi" <pannuz@gmail.com> wrote: >Hi all, > >One of my User has issue about one of the folder being accidently >deleted or disaapeared from his Mailbox while ...

receive email messages sent to a new user account
i have set up a new user account for email and now when I send a message to them I receive a copy of the same message in my inbox. i have reviewed the account settings and all appears normal. am I missing something? what should I do as I should not be receiving messages addressed to somebody else. ...

bp 10 login as a different user
Hello, I�m using Dynamics GP 10 and Business Portal. The problem is that I cannot use the login as a different user option. IIS tells me that i don�t have sufficient rights to read the pages. The thing is that loging in as that same user in windows and then accesing BP it works fine. It�s just the "Log in as a different user" option. Any ideas will be appreciated. Thanks Eduardo Eduardo, Thanks for using the newsgroups. We recommend that customers either have Internet Explorer prompt them for credentials (Tools > Internet Options > Security > Custom Level >...

How do I format a cell, so the user must use a user calendar?
I created a user calendar by means of a macro. I want to set a cell in the workbook so when someone clicks on the cell the calendar automatically comes up so they can select a date. nathan See Ron de Bruin's site for code. Adjust the range to suit http://www.rondebruin.nl/calendar.htm Gord Dibben MS Excel MVP On Mon, 16 Jan 2006 09:25:03 -0800, nathan <nathan@discussions.microsoft.com> wrote: >I created a user calendar by means of a macro. I want to set a cell in the >workbook so when someone clicks on the cell the calendar automatically comes >up so they can se...

How to delete text in word doc based on user selection
I'm trying to create a word template that will have a form appear at the start and depending on what checkboxes are selected by the users the document will delete certain paragraphs. ie The Form asks the user to select which paragraphs to use and if they only select 1, 2 & 3 then those will remain but all others will be deleted. This is a 'once size fits all' Statement of Work document but only certain sections are relavent depending on what the user selects. I've got the form done, I just need to know how to acheieve this? I'm thinking of selecting ea...

SQL 2000 Failover Cluster
Hi all. With the site being upgraded, I can't search so forgive me if this has already been asked. Does anyone know of or has anyone experienced problems with GP 7.5 running on a SQL 2000 failover cluster? Thanks! Dana Hi I have installed a number of SQL / Great Plains on a clustered environment. We always recommend to use the Active/ Passive cluster configuration to enable true failover (some clients wanted Active/Active Clustering and it can cause a few problems with some MBS web services products) When testing SQL Cluster Failover, we found it takes roughly 10 to 15 seconds ...

Upgrade Advisor: Duplicate SQL Server SPN
I saw a thread about this earlier where someone said just restarting the SQL Server service would fix this (it doesn't). Has anyone come across this? Is is necessary to use the ldp.exe tool to locate the duplicate SPN and use adsiedit.msc to remove it? -- Matt Wittemann http://icu-mscrm.blogspot.com By the way: I am getting this error on a Small Business Server 2003 system. (Yet another upgrade on an SBS with SQL troubles!). The KB article I found about duplicate SPNs with CRM 1.2 refers to a fix for Windows 2000 that does not seem to apply in this case: no ldp.exe tool on SBS 2003...

Tracking emails sent to a pubic contact from all users
I'm a novice to Exchange 2003. I've managed to set up public contact folders that all users have access to. But I would like to be able to open a contact in that public folder and go to activities and see the emails sent by all users to this contact. Is that possible. Er - Exchange doesn't offer anything like that in the way that you are asking. Not to mention the "Activities" section is an Outlook-specific function. From what I can tell, the Activities section has to point to a specific mailbox, not all users. The best thing you can do is make sure that you ha...

want to hide a tab based on the current user role
I have hid many tabs based on sleection in a pul-down in CRM, and understand the basics, but what I want to do is hide a tab based on teh current security role: example; if the CURRENT logged on user belongs to the System Administrator, THAN show tabx. Does anyone know how to do this? Im no programmer -so any help will be much appreciated. Randall Smith Randall, Ronald Lemmen's post here will be of help: http://ronaldlemmen.blogspot.com/2006/05/finally-there-show-and-hide-fields.html Just note that the above won't work if the user using the Laptop/Offline version of the C...

User Defined Fields 01-19-10
In "hacking about", I created several user defined fields which I would like to remove. Under "all fields" they are listed under "User-Defined Field In This Item", "User-Defined Fields In This Folder"and "Mens Club"(custome form). How can I remove these fields. On the All Fields page in the form design, you should see a Remove button on the User-Defined Fields ... views. You may also want to go into the folder's View dialog and remove the field definitions with the Remove button there. Best practice is to do your "hack...

OWA: Viewing user schedules?
Hi there, Is there ANY way to open up users' schedules for meeting planning, etc? It's a snap in Outlook, but through OWA, all I've found is that when you go to invite attendees, you can see if they're available for that specific time. But if one of them has a conflict, you've got to go back through the process of setting up another meeting at another time, hoping that a conflict won't pop up with the new one either. So is there any way to just see the schadules for everyone through OWA? Thanks ...

rownum update sql
Hi all, I need to scrub some data prior to shipping it off to a vendor for them to debug their code. I want to update all the rows in the table with the rownumber as part of the data. I know in Oracle, for example, I can use rownum. So, for example, for my name field, I want to use Name1, Name2, Name3 ....NameXXX. How do I write an update statement that will update the column to include the row number in the data ? Many thanks ! Mark ...

Collections Management
Dynamics GP v9 - Collections Management Users are unable to use "user defined letters" (Word Documents) for mass mailings. They are limited to only using predefined letters, or printing each letter individually for every customer. Error received when users attempt to use a custom letter: -------------------------------------------------- Unhandled script exception: Cannot find report "COL_Reminder_UpcomingDue". EXCEPTION_CLASS_SCRIPT_MISSING SCRIPT_CMD_REPORT -------------------------------------------------- ---------------- This post is a suggestion for Microso...