October 17, 2018 at 9:24 pm
Hi,
There has been a proposal to remove all server level permissions to the public role as part of a compliance recommendation.
For example;
REVOKE VIEW ANY DATABASE FROM public
REVOKE CONNECT ON ENDPOINT FROM public
If I do this, won't I just need to add these permissions to every Login I create, and if so wouldn't I just be creating extra work with no real additional security advantage?
Or is there something that I'm missing?
What would be the better practice to give users access to the databases using the principal of providing the minimum privileges to perform their role?
E.G. I can't make them database owners just to view the databases.
We are currently in the testing stage of migrating to SQL 2017 which gives us the opportunity to trial these changes if necessary.
Thanks,
October 18, 2018 at 8:06 am
Andrew_Robertson - Wednesday, October 17, 2018 9:24 PMHi,
There has been a proposal to remove all server level permissions to the public role as part of a compliance recommendation.
For example;
REVOKE VIEW ANY DATABASE FROM public
REVOKE CONNECT ON ENDPOINT FROM publicIf I do this, won't I just need to add these permissions to every Login I create, and if so wouldn't I just be creating extra work with no real additional security advantage?
Or is there something that I'm missing?
What would be the better practice to give users access to the databases using the principal of providing the minimum privileges to perform their role?
E.G. I can't make them database owners just to view the databases.
We are currently in the testing stage of migrating to SQL 2017 which gives us the opportunity to trial these changes if necessary.
Thanks,
Is there a business reason that all logins would need to view all the databases on the instance? If that is the case then you would be adding it back to all logins. But that would likely be an unusual case. Logins would need to connect to the endpoint they are using but they wouldn't be using VIA would they? Do they need to connect on all endpoints?
Removing those permissions are part of the requirements for the US government entitires and contractors following STIGs so it's been used. There is a MS blog that discusses this and has a script for removing server as well as database level permissions from guest and public which is worth reading in your case:
Remove Public and Guest Permissions
There are not that many permissions to public at the server level - the connect endpoints and view any database. I would guess that just the endpoints could be an issue. You can find the error messages related to the endpoints in this blog:
Why do I get the infrastructure error for login failures?
Sue
October 22, 2018 at 2:22 am
Sue_H - Thursday, October 18, 2018 8:06 AMAndrew_Robertson - Wednesday, October 17, 2018 9:24 PMHi,
There has been a proposal to remove all server level permissions to the public role as part of a compliance recommendation.
For example;
REVOKE VIEW ANY DATABASE FROM public
REVOKE CONNECT ON ENDPOINT FROM publicIf I do this, won't I just need to add these permissions to every Login I create, and if so wouldn't I just be creating extra work with no real additional security advantage?
Or is there something that I'm missing?
What would be the better practice to give users access to the databases using the principal of providing the minimum privileges to perform their role?
E.G. I can't make them database owners just to view the databases.
We are currently in the testing stage of migrating to SQL 2017 which gives us the opportunity to trial these changes if necessary.
Thanks,Is there a business reason that all logins would need to view all the databases on the instance? If that is the case then you would be adding it back to all logins. But that would likely be an unusual case. Logins would need to connect to the endpoint they are using but they wouldn't be using VIA would they? Do they need to connect on all endpoints?
Removing those permissions are part of the requirements for the US government entitires and contractors following STIGs so it's been used. There is a MS blog that discusses this and has a script for removing server as well as database level permissions from guest and public which is worth reading in your case:
Remove Public and Guest PermissionsThere are not that many permissions to public at the server level - the connect endpoints and view any database. I would guess that just the endpoints could be an issue. You can find the error messages related to the endpoints in this blog:
Why do I get the infrastructure error for login failures?Sue
Thanks Sue, it looks like there is only one endpoint required for most of the databases, and it makes sense not needing to give view any database after reading your comments.
October 22, 2018 at 5:38 am
Sue_H - Thursday, October 18, 2018 8:06 AMIs there a business reason that all logins would need to view all the databases on the instance? If that is the case then you would be adding it back to all logins. But that would likely be an unusual case. Logins would need to connect to the endpoint they are using but they wouldn't be using VIA would they? Do they need to connect on all endpoints?
Removing those permissions are part of the requirements for the US government entitires and contractors following STIGs so it's been used. There is a MS blog that discusses this and has a script for removing server as well as database level permissions from guest and public which is worth reading in your case:
Remove Public and Guest PermissionsThere are not that many permissions to public at the server level - the connect endpoints and view any database. I would guess that just the endpoints could be an issue. You can find the error messages related to the endpoints in this blog:
Why do I get the infrastructure error for login failures?Sue
Actually, the requirement in the DISA STIGs to revoke view any database and connect SQL were removed from the SQL2008 / 2008R2 checklists a couple releases ago, and are not in the SQL2014 and newer STIGs.
(Source: I'm the guy who successfully convinced DISA that it was kind of a dumb idea to revoke the permission to connect to the SQL Server instance only to have to manually add to back to each and every login...)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply