Solved

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

Posted on 2011-02-28
13
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Re-appearing SQL Server Agent jobs 7 46
query optimization 6 30
Getting local user timezone in Sql Server 5 40
Substring works but need to tweak it 14 35
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 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