May 17, 2019 at 7:03 pm
We have a financial application (Dynamics GP) with a SQL 2014 back-end. Users of GP are in a security group that has write/edit permissions. Ownership is asking me to give one user, who is a member of that group, read-only access to the SQL tables so he can connect via MS Access and ODBC. SQL has Windows authentication and so nothing I have tried stops him from having the ability to edit data via Access.
Any suggestions?
Thanks
May 17, 2019 at 7:06 pm
Out of interest, what is stopping him from connecting right now?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2019 at 7:17 pm
You can explicitly deny write access to his specific login.
Wouldn't that also (badly) affect his rights in GP?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2019 at 7:21 pm
ZZartin wrote:You can explicitly deny write access to his specific login.
Wouldn't that also (badly) affect his rights in GP?
Yeah the deny on his personal login would trump the read/write group access which may or may not be intended. So if the idea is for him to keep read/write access but only have read access in Access :p another option would be to make a SQL login with read only access and use that for his ODBC.
May 17, 2019 at 7:28 pm
Phil Parkin wrote:ZZartin wrote:You can explicitly deny write access to his specific login.
Wouldn't that also (badly) affect his rights in GP?
Yeah the deny on his personal login would trump the read/write group access which may or may not be intended. So if the idea is for him to keep read/write access but only have read access in Access :p another option would be to make a SQL login with read only access and use that for his ODBC.
Which would work ... as long as he does not twig that he can also log in directly using Windows Auth.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2019 at 7:43 pm
ZZartin wrote:Phil Parkin wrote:ZZartin wrote:You can explicitly deny write access to his specific login.
Wouldn't that also (badly) affect his rights in GP?
Yeah the deny on his personal login would trump the read/write group access which may or may not be intended. So if the idea is for him to keep read/write access but only have read access in Access :p another option would be to make a SQL login with read only access and use that for his ODBC.
Which would work ... as long as he does not twig that he can also log in directly using Windows Auth.
Which kind of raises the question of why he would need to have different permissions in access vs whatever other application. Or if there are only certain tables he needs access to deny write access on everything but those.
May 17, 2019 at 7:49 pm
Which kind of raises the question of why he would need to have different permissions in access vs whatever other application. Or if there are only certain tables he needs access to deny write access on everything but those.
I think it's a fairly safe bet that he uses GP for controlled and audited data modifications (OLTP), and wants to use other tools (eg, Access) for read-only reporting purposes.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 17, 2019 at 8:03 pm
I think it's a fairly safe bet that he uses GP for controlled and audited data modifications (OLTP), and wants to use other tools (eg, Access) for read-only reporting purposes.
Even though it's a request for reads, that can actually be risky in terms of audits and this being a financial application. The poster should make sure they have the request in writing - change ticket, email or something. And make sure whoever is requesting the extra access is aware of the risk issues as well.
Sue
May 22, 2019 at 1:05 pm
If GP does all its writes via stored procedures, you can deny write to his login and grant execute to the stored procedures.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply