Solved

Setting Permissions on SQL Tables and Getting A log of everything on specific table

Posted on 2011-02-28
13
363 Views
Last Modified: 2012-08-13
Dear all
We have an ERP system which is Microsoft Dynamics GP 10 and database SQL Server 2008
We will start to implement Manufacturing Phase and this phase is known by confidentiality.
In the same time we have our development team which will make a lot of reports that will help in this phase a lot.
So from here I want for some specific tables to make permissions that the development team doesn’t have any access to these tables.
And if I set permissions to specific user, then I need a log to tell me if this user makes any Select/Delete/Insert/Update anything by date and time and what was the result of this select.
I hope I did clear the point very well, please ask me if you need more elaboration.
Thanks,
0
Comment
Question by:RamzyNEbeid
  • 7
  • 3
  • 3
13 Comments
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34996705
Are they Dexterity developers, VS, or VBA?
0
 
LVL 3

Assisted Solution

by:mordi
mordi earned 333 total points
ID: 34996709
There are two options:

    * Triggers on the specific tables
    * Server traces

Of these two options, triggers are probably the best fit. Server traces will tell you the query, who executed it, and when they executed it, but it can't return the information on what the original values were. That's going to require a trigger. Triggers have special tables that are used depending on the operation:

INSERT

    * inserted - Tells you what is being added.

UPDATE

    * deleted - Tells you what the original values were.
    * inserted - Tells you what the new values are.

DELETE

    * deleted - Tells you what is being deleted.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34996729
mordi, SQL DB Triggers are not recommended nor supported in Dynamics GP.
0
 

Author Comment

by:RamzyNEbeid
ID: 34996816
they are Visual Studio devrlopers, eConnect, Continuum API
and we have a lot of devlopement on the databse level, i mean tables, SP and Views
0
 
LVL 10

Accepted Solution

by:
Abdulmalek_Hamsho earned 167 total points
ID: 34997110
The recommended way is to prepare a development environment (testing server identical to your production server) for your development team.

If you insist on using the production server by your development team, you need to do the following (but NOT RECOMMENDED):

1- Create a new role in the company DB; Dev for example.

2- Make your developers members of Dev group/role and not DYNGRP. (I assume the developers IDs have been created in the Dynamics GP, and they're already GP users)

3- Customize the permissions fo reach table by right-click the table -> Properties -> Permission and select the Dev role.

4- Use Dynamics GP Audit Trail module to track every and each process for the developers users.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34997144
Note: You must be an expert in Dynamics GP tables in order to set the permission this way, because you need to know what are the tables involved in every transaction, otherwise you would get an error message in GP that the table is inaccessible.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:RamzyNEbeid
ID: 34997289
what about the Result Set of the Select Statments
i want to know what was the result of the data returned ot the user.

i did use the "Aduits" in SQL server 2008.

but still i need the result of the data returned ot the user.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34997364
Use SQL Profiler.
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34997423
Enabling the C2-Audit in MS SQL Server is an option also.
0
 

Author Comment

by:RamzyNEbeid
ID: 34997639
you mean it can give the Result set of the SQL Statment
0
 
LVL 10

Expert Comment

by:Abdulmalek_Hamsho
ID: 34998010
No, it gives the commands issued to the SQL server, and you would need to run it to see the result set. (I know it won't be as accurate, because if rows where added/deleted after executing the statements, the result will show different data than it was been executed by the developer).

 If this doesn't fit your needs, you would need to look at 3rd-party Audit  Applications.
0
 
LVL 3

Expert Comment

by:mordi
ID: 34998631
The main limitation of the auditing is that it reduces the performance of the SQL Server. This happens due to saving the every action to the file. Second limitation is the hard disk space. These auditing files grow rapidly, which will reduce the disk space. According to the C2 , if it is not able to write to the trace file, SQL Server will be shutdown.
0
 
LVL 3

Assisted Solution

by:mordi
mordi earned 333 total points
ID: 34998675
The trigger approach is the best solution to fulfill your requirements as is said before.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now