• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

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

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
RamzyNEbeid
Asked:
RamzyNEbeid
  • 7
  • 3
  • 3
3 Solutions
 
Abdulmalek_HamshoCommented:
Are they Dexterity developers, VS, or VBA?
0
 
mordiCommented:
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
 
Abdulmalek_HamshoCommented:
mordi, SQL DB Triggers are not recommended nor supported in Dynamics GP.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
RamzyNEbeidAuthor Commented:
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
 
Abdulmalek_HamshoCommented:
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
 
Abdulmalek_HamshoCommented:
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
 
RamzyNEbeidAuthor Commented:
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
 
Abdulmalek_HamshoCommented:
Use SQL Profiler.
0
 
Abdulmalek_HamshoCommented:
Enabling the C2-Audit in MS SQL Server is an option also.
0
 
RamzyNEbeidAuthor Commented:
you mean it can give the Result set of the SQL Statment
0
 
Abdulmalek_HamshoCommented:
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
 
mordiCommented:
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
 
mordiCommented:
The trigger approach is the best solution to fulfill your requirements as is said before.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now