SQL Server Segregate Developers from Database

Posted on 2014-03-16
Last Modified: 2014-03-19
I am in a position to advise a customer on some basic SQL server best practices when it comes to security, development and reporting. This is a bit unusual for me because I tend to do work for businesses that have some in-house database support.

The "main database" is an OLTP database and has a structure that the vendor "advises" the customer to not manipulate. As a general rule, the customer support staff  will have a custom database with db_owner permissions and db_datareader to the main database. With the exception of a few people this is the suggested security setup.

So best practices for security:

How many AD groups with access to what? - I think the main groups DBA, developer and report end users.

Best practices for developer:

Keep the following in the custom database referencing the main databases if possible:

Stored procedures instead of embedded T-SQL
Custom tables
Custom views (I have read to avoid views in favor of SP)
Custom functions
Is it possible to create non-clustered indexes to cross databases? Performance impact? Clustered indexes are already created by main database vendor.
Should there be any consideration given to fully qualified versus schema and synonyms?

Reporting server

Should have separate account with same permissions as developer - for connection to DB's? Is security is pretty straight forward. Admin and User correct?

Anything else?

Question by:mike1142
  • 4
  • 4
  • 2
LVL 78

Expert Comment

ID: 39934087
Best practices would dictate that developers have access to a developer dedicated system on which they can test.

There is a mix of questions that I can not figure out what is being sought.

Usually, if there is a DB that should not be modified, then other than SA/Administrators all others will/should have db_datareader only.
It has happened before that a person looking at data from multiple Dbs mistakenly issues a command in the wrong window. similar to a person remoted into several systems, mistakenly issues a reboot on the wrong one.

reporting systems is less of a security issue, but a performance one, i.e. someone creates a report that pounds the DB degrading performance.

Author Comment

ID: 39934253

Thank you for responding. I do have a mixed bag here but all I really need is a high level "best practice" document. Since it would be bereft of detail maybe it cannot be considered a best practice document

I am looking for general...suggestions is a better word? In three areas, database security, database development and reporting. The slant is toward the technical setup.

I am attaching a redacted draft document that I am working on and I am looking for a few pointers to be complete but not overly detailed.

As I said in my OP this is new territory for me. The vendor dropped a huge database on these folks that they need for statutory reasons but have no resources to manage a large part of it. Where I usually come in is either at a company where SS reporting is already being done against other databases and they just require my specialized knowledge of the database and reporting or they have knowledgeable resources who manage the databases.  

The customer is very small hence my presence. I was not aware of how far off they were. Unfortunately my employer engaged the customer and left me to discovery all of this.

Thank you for any assistance.
LVL 78

Assisted Solution

arnold earned 250 total points
ID: 39934287
In a regulatory environment, you need to check whether they need the SQL built-in auditing activated (C2 audit tracing)
There should be a straight separation.
two sets of systems Production and test/development.
In a situation such as yours, no developer should have any access to the production.
AS the System Sysadmin/DBA, you would be prompted to restore the backup from the production onto the test/development environment.  When changes are approved in the test/developemnt environment that meet the restrictions of the software vendor, A person will have to apply those changes to the production.  Including exporting the reports from the test/development system and importing them into the production.

Depending on the size and versions of the sql server, a suggestion might be made to if SQL server enterprise is in place, mirroring the database to another system where a snapshot of the mirror (read-only) can be used for reporting purposes.
Best practices are one thing, the issue with smaller firms is that the Administrator/DBA is often the one tasked with development as well such that the person has to enforce the rules on oneself.
Register Today - IoT Current and Future Threats

Are you prepared to protect your organization from current and future IoT Threats?  Join our Wi-Fi expert in episode three of our webinar series for a look at the current state of Wi-Fi IoT and what may lie ahead. Register for our live webinar on April 20th at 9 am PDT!

LVL 63

Expert Comment

ID: 39934339
I kinda of like to suggest you catch this if interested, we do want to follow close to standard compliance which can be more common and probably acceptable and giving assurance to your client. The role of developer and DBA is important and need to be managed well with duties and right segregated - so it is not just development and reporting server - target the data segregation and role based access

Specific to AD I tend to see it from audit perspective with some focus stated in the article summary below. You can check out the Auditing section which carries two part namely on the AD GPO and scripts to aid the process and alerting

MS also has the sharing of using schema as best practices:

   Always refer to the objects using a fully qualified name. At the very least, use the schema name followed by the object name, separated by a period (.).
    Simplify the implementation of schemas in a database by using synonyms to abstract the schema ownership of objects.
    Use schemas to protect the base database object from being altered or removed from the database schema by users without sysadmin permissions. User permissions should be managed at the schema level.
    Use schemas to combine related, logical entities into one physical database to reduce administration overhead.

Author Comment

ID: 39934548

I need to clarify the test/prod scenario. Both are fed from the source system which has both a test and a prod "directory" even though the SS database is kind of an OLTP the source system is the true transactional database whose database model is exceptionally suited for the business processes. So in essence we have 2 no-touch SQL databases which necessitates the third for development (actually 4 no-touch in this case).

Wouldn't the developer need reader access to develop the stored procedures?

I like the export/import idea from test to prod for the reports. I'll look into that. So this would suggest two portals and maintaining 2 SSRS regions?

So what do you think of the idea of keeping the development database objects separate from the test/prod databases? What objects would/could be stored in this kind of scenarios, which can't (indexes?) Are there any other suggestions to preempt destruction of the predefined databases (other than backups)?

 breadtan - thank you for the links I did see that final quote. I will look into it more.
LVL 63

Expert Comment

ID: 39935786
quick thought is that sometime folks may even go to extend of having environment such as development, staging and production.

Development state the simulated environment for security testing and code functional testing, the data involved is usually dummy likewise for the report. It may not necessarily do load testing too but just to have some sort of checks before moving into staging

Staging is almost close to the production build and likely to be connected to simulated e-Service required and some even exposed it to internet which can be open to wild and remote user testing. Likewise the process of testing continues but more for acceptance by user finalised requirement fulfilment. there can be some "real user data" provided

Production is the final and it is left to wide deployment and implementation, rollback is immediate if things doesnt work out for the past half day. I do feel the developer access is as per norm user and hardly able go into DB schema etc. DBA has full control at this stage.

Author Comment

ID: 39940178

These documents are pretty close to what I need even with my poorly stated question.

So I  thought of one other way to ask the question. You just "graduated" DBA school and you are at your first job. You find an analyst whose primary focus and background applies to a different side of the business say - customer service. You approach your manager to get the "lay of the land" and he says "Thank god you are here, we have this really huge database with sensitive data to maintain you are my guy please take of this".

What are your top 5 things and then what are the next 10 things?
LVL 63

Accepted Solution

btan earned 250 total points
ID: 39941276
First is really to discover as moving ahead w/o good knowledge for DBA is "dangerous"

a. Identify critical business need and the supporting systems, what are key DB involved storing those critical data? How many type of DB?

    How many production servers/clusters do we have?
    How many instances running on how many servers?
    How many databases do we have?
    How many applications are we supporting on each database server?

b. Review the I/O reads and health state of the big DB systems of (a), any chance of slowness and time to "retire" data and system. Review your query performance and identify the "slow" query and latency - why so?

c. Review the (a) data optimisation e.g. BIGINT data type for that column when a INT would do just fine. Knowing the Index Fragmentation. Any poorly performing query by an index etc? Missing index or unused indexes?

d. Linkage of data e.g.  *many* databases that have little to no primary keys, foreign keys, or even any indexes defined, transaction log history and active execution calls

e. Review security policy and segregation of tables and DB, any enforcement from schema and data masking point of view, any cell and column level protection etc, when is last security error or access alert

After knowing the "big picture" it is to really plan out the action plan with priority to baseline the critical system's supporting subsystem DB. It is hard to touch the production

a. Review staging environment - what exist? what lacking?
b. Review disaster recovery plan
c. Review accounts (who has sysadmin rights? should they have it? appl accounts?)
d. Review audit trails
e. Review scheduled archival
f. Review backup
g. Priortise the action plan - archival/backup-trim the fats, stay healthy, recovery exercise, security validation , audit log review, ...
h. Plan for growth and scaling up - not cascading and buying storage only, any means to tiering the storage (always read vs seldom read)
i. Reorganise and Rebuild if need to.
j. Remember the restarting of SQL Server service in order to “fix” or remedy a problem is NOT an acceptable solution
k. Run CHECKDB regularly
l. Document everything and all the above (again and again)

Author Closing Comment

ID: 39941311
I think this is enough and I have more than I need thank you. I split it because quality and quantity of the information was pretty much split as well.
LVL 63

Expert Comment

ID: 39941356
thank you no worries

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

The related questions "How do I recover the passwords for my Q-See DVR" and "How can I reset my Q-See DVR to eliminate a password" are seen several times a week.  Here we discuss the grim reality of the situation.
Ransomware is a malware that is again in the list of security  concerns. Not only for companies, but also for Government security and  even at personal use. IT departments should be aware and have the right  knowledge to how to fight it.
Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
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 …

685 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