Thread 632-1070294 - Getting SQL error while giving user(s) co access

[swabunn]

Hello,

I'm trying to give some users access to a company. When I do this, I get a SQL Server error: 'User or role '<user>' already exists in the current database.' Click OK, I then get the next error: 'The stored procedure smUsrCmpnyAccssChckAccssCHG returned the following results: DBMS: 15023, Great Plains: 0.

If any one has any ideas or solutions. I'd really appreciated it.

I know my way around Great Plains, it's just the SQL Server just I have trouble with.

Thanks a lot,
Z

QUESTION ON FOLLOWING REPLY: WHERE IS NEW DATABASE, I AM NOT ABLE TO FIND IN DATABASES IN ENTERPRISE MANAGER. IT HAS ONLY NAME OF DATABASES WHAT WE HAVE CREATED.


barbola (TechnicalUser) 2 Jun 05 12:45
1. Launch Dynamics/eEnterprise and login as the sa user.

2. Go to Setup|System|SQL Options and unmark both boxes, choose OK.

3. Go into Setup|System|User Access and unmark the box for the company you are trying to grant access to, choose OK.

4. At the Microsoft SQL Server, go into Enterprise Manager under Databases and select the new database. Go into Groups/Users file and pull up the user and right click on it and choose Delete.

5. Go back into Dynamics/eEnterprise and go into Setup|System|SQL Options and mark both boxes.

6. Now go into Setup|System|User Access and mark the box for the company you wish to grant access to.

Thanks!
Barb E.

 

[jymm]

wow - I just dealt with this one last week

You using SQL2005 or 2000?

Essentially the problem is that SQL has it's security and Dynamics has it's own --- and they are out of sync

 

[swabunn]

sql 2000

-swabunn

 

[jymm]

well - the problem is that you have to find out which database the user has access to and which ones Dynamics 'thinks' they have access to.

1) since it is sql 2000, go into Enterprise manager (make sure you are pointing at the server where your dynamics data is and not a local sql2000 instance) and run this code (fill in the ID you are having problems with) by selecting Tools -> Sql Query Analyzer

select sy60100.UserId, sy01500.interid
from sy60100 inner join sy01500 on sy60100.cmpanyid = sy01500.cmpanyid
where userid = 'PUT USER NAME HERE'

This will give you a list of company databases that DYNAMICS thinks the user has access to --- and these are the actual name of the db (print off the screen - it will help)

2) Back in Enterprise manager expand down till you see the databases and a folder called 'Security' (near the bottom of the list when you expand the server).

3) expand Security and right click on the user you are having problems with. Select the 'Database Access' tab & scroll through to find the database that is on this list that is NOT on list list from #1 (this new list is what DBs SQL thinks the user has access to). Uncheck the PERMIT box for the 'odd database out' (in my case it was TWO aka The world online aka Fabrikam) - careful of non dynamics databases that the user might have access to...

NOW you should be able to go back into Dynamics and grant this user access to anything since now Dynamics & SQL are in sync.

let me know if you need more help -j