Post reply

SQL Server 2017 as a Service ! How to do ??

  • September 19, 2018 at 11:51 am

    #363711

    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 SQL Server on premises 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...

    Ty a lot for any help !!!

  • September 19, 2018 at 12:27 pm

    #2006325

    Use contained databases

  • September 20, 2018 at 7:57 am

    #2006426

    Joe Torre thank you very much for the tip, I did tests with this feature and it "almost" solved my situation, but it happens that I have 10 bases in the instance and I need to give access to 3 of them, I saw that there is the possibility to create duplicate user bases contained informing the even SID for the users and enabling TRUSTWORTHY in the base that will be informed in the login of the user, but in doing so all 10 bases were accessible to this user... can solve this? give access to only 3 bases ??

    Thank you again !!

  • September 21, 2018 at 3:18 pm

    #2006638

    Individually create 3 contained logins, one for each of the 3 databases with the same creds.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply