October 25, 2007 at 3:23 pm
To all: Is it possible to install only SSMS from the SQL Server 2005 installation disk? We need to provide SSMS to about 30 users via Citrix, and we do not want to give them access to Profiler, Tuning Advistor, etc. I have run through the install several times and cannot find a place to narrow down what user tools get installed. Thanks in advance.
October 25, 2007 at 9:01 pm
DrewTheEngineer (10/25/2007)
To all: Is it possible to install only SSMS from the SQL Server 2005 installation disk? We need to provide SSMS to about 30 users via Citrix, and we do not want to give them access to Profiler, Tuning Advistor, etc. I have run through the install several times and cannot find a place to narrow down what user tools get installed. Thanks in advance.
Within Citrix you can choose only to publish SQL Server Management Studio. You can control access to Profiler and Tuning Advisor by restricting using NTFS permissions (or simply renaming the executables). Therefore, even if they attempt to start the tools from SSMS, they won't be able to do so.
K. Brian Kelley
@kbriankelley
October 25, 2007 at 10:21 pm
Profiler requires alter trace permissions. DTA requires sa rights for the first use, and db owner rights subsequently. These should be sufficient to stop most of your users from running them.
October 26, 2007 at 10:34 am
End Users should not have access to direct SQL. PERIOD! If it is a reporting system some visual tool yes, but not SSMS.
Developers should have read only rights to Production. Even then you are at risk of a bad query impacting system performance.
Development... Have at it. Local DB's perfect for table design testing. Then go through DBA's for development system/table design.
QA only access to QA (not dev or prod).
Production install team (sometimes the same people who support the system) That way they know exactly what changes are made since they have to make them.
October 26, 2007 at 11:08 am
Bob Fazio (10/26/2007)
End Users should not have access to direct SQL. PERIOD! If it is a reporting system some visual tool yes, but not SSMS.Developers should have read only rights to Production. Even then you are at risk of a bad query impacting system performance.
Development... Have at it. Local DB's perfect for table design testing. Then go through DBA's for development system/table design.
QA only access to QA (not dev or prod).
Production install team (sometimes the same people who support the system) That way they know exactly what changes are made since they have to make them.
Yep. All sounds real good. I agree with every aspect. And yet, when the boss's, boss's boss sends the word down... We install the software and then do what's necessary to wall things off & protect the important parts of the system, etc.
"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 26, 2007 at 11:29 am
Bob Fazio (10/26/2007)
End Users should not have access to direct SQL. PERIOD! If it is a reporting system some visual tool yes, but not SSMS.
Right there with you, but see Lowell's sig.
K. Brian Kelley
@kbriankelley
October 26, 2007 at 11:34 am
Grant Fritchey (10/26/2007)
Yep. All sounds real good. I agree with every aspect. And yet, when the boss's, boss's boss sends the word down... We install the software and then do what's necessary to wall things off & protect the important parts of the system, etc.
Don't forget to pound them into submission, i.e. be sure to mention names next time production "goes down", hiccups, stumbles, etc... due to end-user queries.
Our CIO is quite happy now that his name is no longer mentioned next to various SQL server outages 🙂 (there's a standing review meeting going over the good the bad and the ugly with IS, and names do make the report). his "dashboard" tells him what he needs to know, and he's not bringing everyone else down with him.
----------------------------------------------------------------------------------
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 26, 2007 at 11:47 am
Any method of accessing the data has inheirt risks no matter if QA, SSMS, or a custom GUI. It is all about security management and the ability to handle certain risks. One tool in and of itself is no more dangerous than another.
The key is perform a risk assessment of any access you are going to consider, determine the risks that a unacceptable or have consequence beyond the scoop of themselves that may be unacceptable. In many cases it may call for new equipment to allow them what they want but mitigate the root issues so they are not a concern. I see often servers being setup for reporting which receive data from production thru replication, this allows the end user to do ad-hoc reporting without concern of the impact to production.
It's not the tool that is the issue but the acceptability of a particular risk you need to focus on. The tool may provide a quick an painless way to get what the user wants as long as you can mitigate the risks that can cause you problems.
October 26, 2007 at 1:15 pm
Antares686 (10/26/2007)
Any method of accessing the data has inheirt risks no matter if QA, SSMS, or a custom GUI. It is all about security management and the ability to handle certain risks. One tool in and of itself is no more dangerous than another....
Security is obviously necessary to protect you from a large aspect of this.
However, a simple GUI can be added to make sure that the user doesn't try to submit an update on the whole table.... Or at least capture it in a transaction. Writing the SQL through SSMS is a lot different.
And to all the others reguarding what you should do, and what you have to do (because they told me to 🙂 ) I have been at this long enough that I had to explain first to my direct boss that the "Senior DBA" who knew less than our "Jr. DBA" accidentally decided it was a GOOD idea to start the recovery of a 800G database at 7am on a Tuesday because they thought it was corrupted because of a misread error message.
Then I explained in the meeting with the Senior VP of IT that the database experienced some corruption, and the restore should be finished by early afternoon..... I left out that the corruption was caused by the restore 🙂
November 9, 2007 at 2:11 pm
I see everyone gets all excited about banning tools like SSMS from the users desktop like that is verboten. What most IT Managers fail to see, is an opportunity to strengthen and grow their empire!
Risk studies would show that ANY access to the database from any program other than approved software is inherently too much of a risk cost, therefore, it makes more sense to hire a developer/report writer to create reports, data extractions etc that are necessary.
Have to look at it from a positive point of view. If you ask the CIO if having the database(s) down for hours because Betty jean in accounting decided to leave off the "where thingie", and failed to pay the executive bonuses on time, I am pretty confident that the extra manpower gets approved.
Works for me.
November 10, 2007 at 10:14 am
Kevin Sehl (11/9/2007)
Have to look at it from a positive point of view. If you ask the CIO if having the database(s) down for hours because Betty jean in accounting decided to leave off the "where thingie", and failed to pay the executive bonuses on time, I am pretty confident that the extra manpower gets approved.
Especially if you have that long running queries trace going which showed Betty Jean ran that query. 🙂
K. Brian Kelley
@kbriankelley
November 12, 2007 at 6:16 am
Oh believe me, we can nail Betty Jean's hide to the wall with the traces.
"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
November 13, 2007 at 11:04 am
I'm glad to see this topic discussed. We have the same problem here that anyone who can say 'sequel' ends up requesting Management Studio, Enterprise Manager, or Query Analyzer. I see two major problems. First, end-users only know how to write 'select * from'. This could cause a great deal of pain for the database with very large tables. Second, and a much bigger issue, is that end-users usually know nothing of joins or the relationship between the tables. They end up writing reports and then make business decisions on wrong information. Bad idea.
So far I've been able to control distribution of these applications--with manager support of course. If you are not a developer or a systems analyst, then you can't have them. So far anyway 🙂
November 13, 2007 at 11:10 am
That is the only way to go IMHO. When Microsoft put Access in the hands of every user with the advent of Office 95, suddenly everyone is a dev wannabee and DBA wannabee because they can throw their recipe manager together in Access. Its a horrible tool, horrible idea and makes it increasingly hard to manage users and their needs. It was much better when IT was 'voodoo' and only the experts got to play in the playground.
And dont mistake my cynicism with dumbing down the users, I have some incredibly bright users, but that still doesnt eliminate the fact they have little understanding or worse, complete misunderstanding about SQL syntax, its nomenclature and its command library. I cant walk into Finance and begin telling them their accounting practices and I sure as heck cant tell them how to calculate taxes. Likewise, they shouldnt be running adhoc queries.
/shrug - off to remove Access from another machine.
November 14, 2007 at 12:16 pm
In 2005 you can add a DDL Trigger to prevent table alters and drops:
create trigger [NoDrop]
on database
for drop_table, alter_table
as
print 'See the DBA before dropping and changing tables'
rollback
At least this will prevent a restore because of end users having too powerful tools.
You could even make the the message 'See the DBA before dropping and changing tables - ya big dummy!'
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply