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

SQL Server Segregate Developers from Database

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?

  • 4
  • 4
  • 2
2 Solutions
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.
mike1142Author Commented:

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.
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.
Managed Security Services Webinar - March 15

Selecting the right managed security services platform to grow your business can be a huge undertaking. Join WatchGuard and Frost & Sullivan in an upcoming webinar as we dive into the key elements of selecting a vendor platform and partnership to fuel a successful MSSP business.

btanExec ConsultantCommented:
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.
mike1142Author Commented:

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.
btanExec ConsultantCommented:
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.
mike1142Author Commented:

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?
btanExec ConsultantCommented:
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 lastly....review all the above (again and again)
mike1142Author Commented:
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.
btanExec ConsultantCommented:
thank you no worries
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now