SQL Server Segregate Developers from Database
Posted on 2014-03-16
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 views (I have read to avoid views in favor of SP)
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?
Should have separate account with same permissions as developer - for connection to DB's? Is security is pretty straight forward. Admin and User correct?