2

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...

9

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.

  • David thank you very much for your response! I also use this script, these permissions because the user still continues to see all the server properties. Is there any way to hide? – Vecchiato Sep 19 '18 at 11:23
  • There is not a way to deny xp_msver without breaking the SSMS object explorer. This is considered public information for SQL Server users. You can add a feedback item for SSMS for it to better support users on servers where PUBLIC has reduced privileges. feedback.azure.com/forums/908035-sql-server – David Browne - Microsoft Sep 19 '18 at 16:41

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.