Solved

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

Posted on 2011-02-28
13
362 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

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.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

12 Experts available now in Live!

Get 1:1 Help Now