Question: As a DBA/BI Developer, should we install SSMS on the PCs of power-users of business data? What are the risks with this approach?
Context: I love SSMS. It's ergonomically designed and enables not just the exploration and management of the SQL Server, but also the data within it (e.g. select/edit rows)
Our business users are not interested in the server, just the data. Some of them grasp the data models but are limited in what they can do with the data by the production system interfaces. We are initiating BI projects to improve data access in the medium-term.
In the short-term, a quick install of SSMS 2014, a Windows Authenticated Login and User with minimum required permissions, and some training would appear to satisfy some of our data management requirements. Some of the users can already write basic SQL.
You can do it but you cannot grant the users anything but very limited permissions. Do not grant the dbo privileges or even worse SA. Take the time to really lock down what they can do (except for select) and be very careful about what SQL server groups you put them in (if any).
Even on selects you should think about (i.e. don't do it) putting them in the db_datareader group which will allow them to read any table in the database. You can revoke permissions but you may forget to revoke read from at view they shouldn't see. I would grant them limited permissions and as they complain add, if appropriate, more permissions.