Sql-server – How to set up SQL Server 2017 as a service

sql serversql-server-2017

I have a question about what would be the best way to set up SQL Server 2017 as a service.

I have clients that buy a SQL Server database as a service, as if it were an Azure or AWS instance, where they do not have access to the server. They only have a user and password in the instance, and I need to hide from those users the server configurations that are accessible through SQL Server Management Studio or by T-SQL commands, such as memory allocated to the instance, number of CPUs, etc.

In SQL Server 2016 I accomplished this by removing the permission on the master..xp_msver view. Without this permission they cannot, for example, see instance properties (right click on the server in SSMS) and some data from the Facets form. Even without this permission they can log on and doing everything they need in their databases.

But in SQL Server 2017 this no longer works, and if I deny permission from this view it is no longer possible to log in SQL Server.

I know it may not be correct to use the standard SQL Server as a service, maybe it has not been prepared for this, but is there a right way? Or what alternatives can be used?

I've spent a lot of time on Google trying to find alternatives, but for SQL Server 2017 I did not find anything that hides the server settings.

Note: My users use SQL Server Management Studio…

Best Answer

Revoking that one view was never a good way to protect your instance. The most straightforward way to do this is to make the user the owner of their database, but have no other rights on the instance, even to list the other databases. EG:

use master 
create database a 
create login a_user with password='P@sssword*****'
alter authorization on database::a to a_user

revoke view any database to public

Now a_user will be able to connect (with SSMS or otherwise) and will see only one database.

Related Question