sql management studio

Question

hi, 

i have to give a recommendation to a client.

what is the best solution? users connecting by remote desktop or installing management studio in each end-user computer to connect to sql server?

why?? because of security? because resource consumption?

thanksss

Answer 1

However, this works perfectly if the user is the owner of the database. Look
that …

• Create a new SQL login "login1"
• Create a user named “login1” in master database
• Grant CREATE DATABASE to login1
• While impersonating login1, create a database called “dbteste”
• Revoke CREATE DATABASE permission from login1
• Revoke VIEW ANY DATABASE permission from PUBLIC
• Register this server as login1
• From the “login1” session, expand database tree. Now, you should see
master, tempdb, dbteste
• Grant VIEW ANY DATABASE to PUBLIC
• From the “login1” session, you should see all the databases

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

I would not allow any client to connect to the server by remote desktop except if they really need it and there's no other way around such that they can't corrupt the computer.

The should install SQL Server Management Studio and connect to the SQL Server but you should strictly control what they can do within SQL Server - you never know how serious those client behave.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

can you tell me why you would not allow any client connect by remote?

thankss

Answer 4

can you tell me why you would not allow any client connect by remote?

if the clients can't log onto the server (OS) by remote desktop they can't harm the server unintentionally or with intent because they've no access to the server. (but you need ensure that they can't acccess the file system remotely tool)

There's no reason why a client using SQL Server needs access to the Windows Server itself.

---

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

Remote Desktop also allows tunneling of different things (like filesystem, etc) to the server. In general the access to a server should be as limited as possible.

Answer 6

Hi,

if you want to manage your SQL instance from another computer, rather than from your SQL server you need to enable TCP/IP for your SQL instance in SQL server configuration manager, under the option SQL Server Network configuration.

When it comes to if you should install SQL management studio on the clients computers - I would say: It depends!

Is the SQL server located in a secure network? If the server is placed in secure network, I would not recommend that you allow SQL trafic from all clients. From my experience with this kind if cases I have used a management computer(such as terminal server(s)) where SQL management studio have been installed. If you are using Windows authentication(as I hope you do): create a (Active Directory) group and create a SQL server security login for this group. I am not a big fan of giving out SQL server Sysadmin rights to all my users, if some users need this I would recommend that you should create a separete (Active Directory) group for Sysadmin rights.

If both the servers and clients are located in the same subnet and there is no conserns to safe zones etc. you should to the same:
create a (Active Directory) group and create a SQL server security login for this group. I am not a big fan of giving out SQL server Sysadmin rights to all my users, if some users need this I would recommend that you should create a separete (Active Directory) group for Sysadmin rights.

Hope this helps you!:-)

/Håvard

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.