0

On a shared cloud we would like to work with one instance of MS SQL Server. Different clients have various databases. They can be owner of their databases and with DENY VIEW ANY DATABASE TO PUBLIC in SSMS they can only see their databases. That works fine. To connect to SSMS they need the CONTROL SERVER permission. But even after a DENY of all the server level permissions they are able to do many things in SSMS, like restart the sql server services. Of course this should not be possible for such users. How can a user be configured not to be able to do any actions to the server itself?

  • 2
    CONTROL SERVER is an administrative permission, you don't need that to connect with SSMS. Any user that can connect to the server can also connect using SSMS, although certain administrative features and tasks will be limited by permissions, obviously. – Daniel Hutmacher Mar 16 '15 at 13:35
  • The problem is that such a user can do too much, fe restart the sql server services. How can this be prevented? – Reto Mar 16 '15 at 14:11
  • 1
    @Reto - as Daniel mentioned, the user does NOT need CONTROL SERVER to connect using SSMS. Remove that right from the account and the user won't be able to control the server. – Max Vernon Mar 16 '15 at 14:50
  • @Max: adding a login to ssms without any further grants or denies or adding roles gives this user the ability to restart the services from within ssms. seems any user with role public can do that. – Reto Mar 16 '15 at 15:13
1

To connect to SSMS they need the CONTROL SERVER permission.

No, that is not true at all. I hope you didn't learn that from any documentation, blog post or presentation. If you did, you should tell us where, so we can hunt them down and correct it.

In order to connect, they need to be a member of the public role (granted by default to all logins). To do anything aside from connect, they need to be granted explicit permissions or added to server-level roles. To connect to a database, they need CONNECT. To do anything aside from connect to a database, they need to be granted explicit permissions or added to database roles.

If you don't want them to have the permissions inherited from CONTROL SERVER, then don't grant that permission. Simple.

And don't test what an end user can do at the service level by connecting to the server in your copy of Management Studio using their credentials. The ability to perform certain operations (like restarting the service) has nothing to do with accounts or permissions inside SQL Server - your Windows domain account is checked, regardless of what user is connected in Object Explorer. To verify this, go to someone's workstation who doesn't have the ability - via their Windows account - to restart services at the Windows level, and try again. Or create a guest account on your own machine.

  • I just added a new login, no further grants or denies or adding roles. a sql server user with no link to a windows user. just having role public. by now not even linked to a specific database. but he can restart the server by right-clicking on the server node. – Reto Mar 16 '15 at 15:20
  • @Reto but didn't you "need" to grant them CONTROL SERVER to connect using SSMS? – Aaron Bertrand Mar 16 '15 at 15:23
  • (P.S. I think that right-clicking the server node and issuing some of those commands tries to do so through the Windows login, regardless of how you connected to the SQL Server. So if you are "proving" this by connecting your Management Studio to the server, even using their credentials, and restarting the server, try having them actually do it. Also, try some other commands, like expanding Management > SQL Server Logs.) – Aaron Bertrand Mar 16 '15 at 15:26
  • no, no need to give CONTROL SERVER. that was a try by denying all the other permissions to prevent actions to the server. – Reto Mar 16 '15 at 15:36
  • @Reto and again, some of those actions have absolutely nothing to do with the SQL Server account you're connecting with. Go to the workstations of one of your peons - assuming they're not domain or local admins on the SQL Server machine, of course - and have them try to restart the server. I bet you get a different outcome than when you try it while logged in to SQL Server as them. – Aaron Bertrand Mar 16 '15 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.