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?
1 Answer
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.
answered Mar 16, 2015 at 15:06
-
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.– RetoMar 16, 2015 at 15:20
-
@Reto but didn't you "need" to grant them CONTROL SERVER to connect using SSMS? Mar 16, 2015 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.) Mar 16, 2015 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.– RetoMar 16, 2015 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. Mar 16, 2015 at 15:37
CONTROL SERVERis 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.