SQL Server 2017 as a service! How to DO?

Clash Royale CLAN TAG#URR8PPP

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

I have a doubt / situation and would like to know if there is a solution and if you could help me!

I have in my environment some 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, only 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 commands, for example: Memory allocated to the instance and CPUs.

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

BUT in SQL Server 2017 this does not work, if deny permission from this view 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 which alternative 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 hide the server settings.

Note: My users use Management Studio...

sql-server sql-server-2017

share|improve this question

edited 3 hours ago

a_horse_with_no_name

36k769108

asked 4 hours ago

Vecchiato

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  please note, sql is not the same as sql-server
  – Max Vernon
  4 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

favorite

I have a doubt / situation and would like to know if there is a solution and if you could help me!

I have in my environment some 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, only 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 commands, for example: Memory allocated to the instance and CPUs.

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

BUT in SQL Server 2017 this does not work, if deny permission from this view 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 which alternative 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 hide the server settings.

Note: My users use Management Studio...

sql-server sql-server-2017

share|improve this question

edited 3 hours ago

a_horse_with_no_name

36k769108

asked 4 hours ago

Vecchiato

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  please note, sql is not the same as sql-server
  – Max Vernon
  4 hours ago
  

  
  
  
  

add a comment | 

up vote
1
down vote

favorite

up vote
1
down vote

favorite

I have a doubt / situation and would like to know if there is a solution and if you could help me!

I have in my environment some 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, only 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 commands, for example: Memory allocated to the instance and CPUs.

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

BUT in SQL Server 2017 this does not work, if deny permission from this view 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 which alternative 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 hide the server settings.

Note: My users use Management Studio...

sql-server sql-server-2017

share|improve this question

edited 3 hours ago

a_horse_with_no_name

36k769108

asked 4 hours ago

Vecchiato

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

I have a doubt / situation and would like to know if there is a solution and if you could help me!

I have in my environment some 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, only 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 commands, for example: Memory allocated to the instance and CPUs.

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

BUT in SQL Server 2017 this does not work, if deny permission from this view 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 which alternative 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 hide the server settings.

Note: My users use Management Studio...

sql-server sql-server-2017

sql-server sql-server-2017

share|improve this question

edited 3 hours ago

a_horse_with_no_name

36k769108

asked 4 hours ago

Vecchiato

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 3 hours ago

a_horse_with_no_name

36k769108

asked 4 hours ago

Vecchiato

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

share|improve this question

edited 3 hours ago

a_horse_with_no_name

36k769108

edited 3 hours ago

a_horse_with_no_name

36k769108

edited 3 hours ago

a_horse_with_no_name

36k769108

36k769108

asked 4 hours ago

Vecchiato

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 hours ago

Vecchiato

62

asked 4 hours ago

Vecchiato

62

62

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

New contributor

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Vecchiato is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  please note, sql is not the same as sql-server
  – Max Vernon
  4 hours ago
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  please note, sql is not the same as sql-server
  – Max Vernon
  4 hours ago
  

  
  
  
  

1

1

please note, sql is not the same as sql-server
– Max Vernon
4 hours ago

please note, sql is not the same as sql-server
– Max Vernon
4 hours ago

add a comment | 

1 Answer
1

active

oldest

votes

up vote
4
down vote

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.

share|improve this answer

answered 4 hours ago

David Browne - Microsoft

8,334622

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Vecchiato is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217965%2fsql-server-2017-as-a-service-how-to-do%23new-answer', 'question_page');

);

Post as a guest

Name Email

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

up vote
4
down vote

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.

share|improve this answer

answered 4 hours ago

David Browne - Microsoft

8,334622

add a comment | 

up vote
4
down vote

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.

share|improve this answer

answered 4 hours ago

David Browne - Microsoft

8,334622

add a comment | 

up vote
4
down vote

up vote
4
down vote

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.

share|improve this answer

answered 4 hours ago

David Browne - Microsoft

8,334622

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.

share|improve this answer

answered 4 hours ago

David Browne - Microsoft

8,334622

share|improve this answer

share|improve this answer

answered 4 hours ago

David Browne - Microsoft

8,334622

answered 4 hours ago

David Browne - Microsoft

8,334622

answered 4 hours ago

David Browne - Microsoft

8,334622

8,334622

add a comment | 

add a comment | 

Vecchiato is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Vecchiato is a new contributor. Be nice, and check out our Code of Conduct.

Vecchiato is a new contributor. Be nice, and check out our Code of Conduct.

Vecchiato is a new contributor. Be nice, and check out our Code of Conduct.

 

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f217965%2fsql-server-2017-as-a-service-how-to-do%23new-answer', 'question_page');

);

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name Email

Name Email

Name

Email

Name Email

Name

Email