Solved

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

Posted on 2011-02-28
13
364 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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