Posts tagged SQL Developer

We are the people our parents warned us about — T-SQL Tuesday

Its TSQL Tuesday time again, and this month the topic is being hosted by Steve Jones ( Blog | @Twitter )  The topic at hand is related to interacting with business users or more Specifically, “What issues have you had in interacting with the business to get your job done”

We are the people, they couldnt figure out!

We are the people our parents warned us about — Jimmy Buffett


A Different Twist

What If you were the business user who had the business “issues”?  I’ve been the business user that is the subject of every technogeek stereotype.  You know, the one who doesnt really know what they want until they see it.  Yeah, I was/am that guy.  As a matter of fact, Ive been that guy recently.  You see, I have two jobs that put me in that sort of position relatively often.  In my day job I manage a team of Database Professionals (Excellent ones I’d add! )   In my “night” job, I volunteer for PASS.  In both of these roles I often see the need to have something built and anytime something gets built by IT there can be issues. 


In order to succeed at completing your job/project/task its often easiest to go ahead and plan on change.  Change is in my estimation at the root of 95% of all issues when dealing with business needs.  Things change often and no matter how many times you think things are static on both sides of the project equation (requirements vs development) they will surely change again.  Ive found that no matter how thourough I have been in coming up with a decent set of requirements before asking for work to be done, in the end something always changes.  Often I will have no control over those changes but, many times something comes up that changes things and this always causes the “issues” between both sides of a project.  Change is actually a good thing, If every project Ive been involved with were implimented exactly as first envisioned (no changes) I suspect things would be considerably different, and not in a good way!!


After sitting at both sides of the desk, you begin to realize the easiest way to eliminate issues between the people who have needs and wants and those IT Guru’s trying to make them a reality is a very open line of communication and trust.  Once you succeed at opening that communication line, all tasks become easier.  With open communication change becomes less of an issue and dare I say it: a bit more acceptable to everyone.  This is the first line of defense in solving  issues before they even become issues!

We’re not crazy

Despite rumors to the contrary the people who need work done AKA “business users” arent totally crazy and intentionally trying to make everyones lives difficult with constant changes.  Often times we are just as frustrated that our needs (or requirements) are changing as the IT Gurus are for having to accomidate those changes.  Once the communication lines are open between all parties, things get considerably easier and the impact of those changing needs can be efficiently weighed against the timelines, costs, etc of the task/project

Allowing effective developer access to SQL Server

When creating a new application, after going through the entire business analysis & requirements gathering process, normally you wind up with a datamodel that includes many tables and relationships.  By this time, depending on the size of the datamodel/system there has been considerable amounts of time invested on all sides.  We need a way of preserving this investment of time while still allowing developers to do their thing!


Most shops have policies in place for what level of access developers can have in each environment.  In many places I’ve seen, developers are allowed DBO access in development, and some lesser access in the higher environments (read only usually).

After you’ve deployed the datamodel to the physical database in a development environment, before you grant the developer group dbo access consider all of the time/effort that has been spent making the datamodel what it is.  In order to allow the developers to do their jobs but not allow them to modify the actual table/schema layout you can grant a combinations of privileges.

Grant Alter Schema on the schemas where the developers will need to modify database objects (for instance stored procedures and functions)

Grant db_datareader –to allow read access

Grant db_datawriter –to allow write access

Grant Create Procedure, Function, Default, Etc  — Allow developers to do whatever you are comfortable with

Deny Create Table in the database –This restricts all Table based DDL

Optional** Deny Create View, Function, Default,  in the database — Restrict any create/alter permissions as needed. 

Important** Alter Schema permissions will allow Alter of ANY object type in the schema that you havent explicitly used a Deny on

Principle of least privilege

This method has proven effective to allow developers to write Stored procs, Functions & Views while still keeping the actual datamodel (tables and relationships usually) in pristine shape.   You could also mix and match your own grants/denys on certain object types to allow for unlimited configuration without granting the almighty DBO.  Yes, you might say that I’m a paranoid DBA who restricts permissions even in DEV!  Of course my great developers would never change a modeled database thereby forcing my hand into figuring out this lockdown of privileges

Go to Top