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?

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.

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.

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.

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

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.

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).

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.

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.

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".

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

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…

Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message. In the To field, type your recipient's fax number @efaxsend.com.
You can even send a secure international fax — just include t…