October 20, 2007 at 7:13 am
Hi,
When I was lad, DBA's were given access to the DBA tools/GUI's etc and the users had an end user query tool if neccessary, maybe built into their app, or similar.
Nowadays, in our organisation at least, every man and his dog seems to want SQL Server Management Studio on their desktop, whether they're developers, end users or even Security Guards 🙂
My argument against it is down to security, albeit the DBA team should manage the levels of access to databases and data sets those non-DBA users want access to, but also that there are several other tools which developers/users can use to query their data eg. Query Analyser (can be used on 2000 and 2005 databases), Visual Studio etc.
Is the argument now that because SQL Server Management Studio encompasses all the 'family' of SQL Server products it should in fact be available to all levels of users, DBA's, developers and end users with their levels of access tightly controlled by the DBA team.
Would it be 'over the top' to restrict access to the DBA team and expect developers and end users to use other methods to access their data eg. Query Analyser?
Anyone any thoughts?
Regards,
Alan.
October 20, 2007 at 10:08 am
A developer is not an end user this is one thing software engineering companies get right give the developer the developer edition so the Management Studio used is the local one so the database is just a data source. What do you know about RDBMS that I don't know?
In this setup developer cannot access the Microsoft SQL Server folder in programs and can only view properties of the databases you have given access. When developing reports Management Studio was a big help for planning set based solution to cut down on crappy T-SQL reporting services will reject.
I am talking from experience I used the most expensive VS2005,all I do in Management Studio get code to parse which means it is either good to go or almost there. If my own Management Studio is not an option you need another developer.
Kind regards,
Gift Peddie
October 21, 2007 at 3:51 am
Gift,
Thanks for the reply.
I appreciate that a developer isn't an end user and yes it's important to us to make sure we give the developers the tools to do their job efficiently without impacting too much on DBA resource.
Regards,
Alan.
October 22, 2007 at 6:41 am
DBA's & Developers, yeah, they should get it because it does have the tools they need to get the job done.
End-users... Why on earth are the end-users generating ad hoc SQL? I mean, if they are, yeah, they should have SSMS or Visual Studio or a 3rd party query tool. But the bigger question is, why are the end users writing SQL at all?
Having said that, I have one team of 6 end-users in the company (of 3000+) that does have it. They shouldn't, but they had enough political pull that we gave it to them, along with an isolated environment so that they can burn their own house down with affecting the rest of the company. So far, in 3 months, they've only burned it down once. Not too bad.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2007 at 6:54 am
Grant,
Thanks. Yes, our situation is very similar, we have some data analysts working on a well supported project who have been given MSSS and now anyone who can spell s..q..l... in the organisation wants it.
As you say, as long their house is isolated...... I like the analogy.
Regards,
Alan.
October 22, 2007 at 7:03 am
Yeah, I mean, we had every single possible good technical, security and performance reason not to give them what they asked for. However, they trumped all those with a phone call to the CEO, who called the VP in charge of IS, who called my boss, who called the dba's. We installed the software.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2007 at 1:08 am
Hi,
In my company we're starting to install ssms to end-users.
In many departments they have so much information to manage that msaccess is not enough. So, as we already had mssql servers, we decided to use them to store that information. We also have oracles but the administration is "out-sourced", but mssqls are administered by us.
They will adapt their programs (they also write programs in vb, usually embedded in access dbs), so they'll be soon using our servers. Users are taking the most basic courses of microsoft to learn how to take advantage of mssql capabilities.
We've also decided to use reporting services as the tool to create their own reports.
Our users must be able to do all this (sqls, programs, etc.), so the discussion was the tools. We already have the technology, now we'll start using it.
We're just starting this, we'll see if this approach works.
I wonder if there are many people in this scenario: users that must have access to the data, to write queries and custom programs, and how did they solve it.
Regards
October 24, 2007 at 7:55 pm
It's a tool. Unless there is a training impact, let them have the tool. From a security perspective, trying to keep them from using that tool means they'll go and find another. And if the permissions are there they will do what they want anyway. Now, if you install the tool and there is no one to train the user on the tool (meaning they're looking at you), that's a different story. But that's also a different argument, one which is potentially winnable. 🙂
K. Brian Kelley
@kbriankelley
October 25, 2007 at 7:03 am
I don't know. I lost the training argument too. I'm looking up at the two book set of structured classes, lesson plans, documentation and tests that I had to spend a frantic three weeks drawing up after I was selected as the trainer for the end users that wanted to learn SSMS. Nice little accomplishment, but one I could have passed on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2007 at 9:37 am
In our case, we made it clear that we would install it, but we would not give them support nor training, support meaning help to make the reports, etc.
For the moment with the training part it seems we're successfull ;), we'll see on the other one.
October 25, 2007 at 9:54 am
We did win the support argument. We've pretty much told them, if they tank the system, our only troubleshooting method will be a restore or restart the server. No other support supplied. I wouldn't have done it if not for that. I worked that way once, never again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2007 at 10:59 am
I'd have to agree with Grant - it very quickly becomes an unsupportable solution. You're just asking for trouble.
SSMS given to an unqualified user (even with only read capability to the appropriate tables) is like a Denial of Service attack in a box. I don't know many end-users who can create syntactically correct, never mind well-performing, queries on the fly. Same and user with a SQL for dummies book, even worse - they have the tools but not the knowledge.
All it really takes is for a few bad CROSS JOIN's with no linking info on a "production-size" data table, or some badly designed counting report, and....there goes your tempDB. Or your applications start timing out because your ad-hoc queries are sucking the life out of the server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 25, 2007 at 11:17 am
They've already had several instances of "Oh, I forgot to add that WHERE thingie to the query."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2007 at 11:23 am
Matt Miller (10/25/2007)
SSMS given to an unqualified user (even with only read capability to the appropriate tables) is like a Denial of Service attack in a box. I don't know many end-users who can create syntactically correct, never mind well-performing, queries on the fly. Same and user with a SQL for dummies book, even worse - they have the tools but not the knowledge.All it really takes is for a few bad CROSS JOIN's with no linking info on a "production-size" data table, or some badly designed counting report, and....there goes your tempDB. Or your applications start timing out because your ad-hoc queries are sucking the life out of the server.
Right, don't disagree, but it's not the tool that is the problem. It is the permissions combined with the lack of knowledge. And if you ban SSMS you've got to look at banning Excel, banning Access, banning all 3rd party tools they might happen to download and try like ApexSQL Edit, I think you see where I'm going with this. You can't win when you start trying to ban the tool. There are too many tools. And in case you're thinking, "Well, if they don't have admin rights, so they can't install apps," that's correct, but some apps don't require installation. So you're still in the same pickle.
K. Brian Kelley
@kbriankelley
October 25, 2007 at 12:08 pm
Point is - I'd advocate actually leveraging reporting services, Crystal reports, Business Objects, or something like that to steer them in the right direction. I mean - that's what they're for? If they want ad-hoc reports/queries, then give them a reporting solution, be it an off the shelf one or something home-grown.
Insulate your data - SSMS just doesn't provide "enough help" to the uneducated user to allow them to do anything in there wihtout endangering the enterprise. I'd be surprised if you couldn't satisfy most people's needs with twenty or so smartly built, parameterized queries that you built. A reporting form built on the company intranet to pull data from those would be maybe 1 week's worth of work? To save you years of headaches? Sounds like ROI to me.
And - if reporting is importing, then give them access to a COPY of the data. Somewhere else. Away from production. Build the framework for them to get what they want, sure. just put a governor on it, and try to save yourself the headache.
If the need isn't important enough to be raised up and turned into a formal report/data request, then steps need to be taken to ensure that it won't ever take precedence over the primary goal of your enterprise assets (a.k.a support enterprise apps).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply