September 9, 2012 at 5:35 pm
MissTippsInOz (9/9/2012)
opc.three (9/7/2012)
You can DENY VIEW ANY DATABASE (REVOKE is not enough)..actually REVOKE is enough I believe. Certainly worked when I tested because it was something I thought might just achieve what the OP required; simply REVOKE against ANY database, then specifically add in permissions at a DB level. However, REVOKE VIEW ANY DATABASE has the same effect as DENY in terms of what a user can see in SSMS.
I tested REVOKE and it was not enough.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2012 at 5:48 pm
opc.three (9/9/2012)
MissTippsInOz (9/9/2012)
opc.three (9/7/2012)
You can DENY VIEW ANY DATABASE (REVOKE is not enough)..actually REVOKE is enough I believe. Certainly worked when I tested because it was something I thought might just achieve what the OP required; simply REVOKE against ANY database, then specifically add in permissions at a DB level. However, REVOKE VIEW ANY DATABASE has the same effect as DENY in terms of what a user can see in SSMS.
I tested REVOKE and it was not enough.
What happened?
September 9, 2012 at 7:10 pm
Here is my test code polished up for public consumption:
----------------------------------------------------------------------------------
-- step 1: run these commands as a sysadmin
USE [master]
GO
IF EXISTS ( SELECT *
FROM sys.databases
WHERE name = N'TestViewAnyDatabase' )
DROP DATABASE [TestViewAnyDatabase]
GO
CREATE DATABASE [TestViewAnyDatabase];
GO
IF EXISTS ( SELECT *
FROM sys.server_principals
WHERE name = N'TestViewAnyDatabaseLogin' )
DROP LOGIN [TestViewAnyDatabaseLogin];
GO
CREATE LOGIN [TestViewAnyDatabaseLogin] WITH PASSWORD = 'welcome#1';
GO
----------------------------------------------------------------------------------
-- step 2: create new connection in Object Explorer using TestViewAnyDatabaseLogin
-- and expand Databases tree item
-- results: all databases are in the list
----------------------------------------------------------------------------------
-- step 3: run this command as a sysadmin
USE [master]
GO
REVOKE VIEW ANY DATABASE TO [TestViewAnyDatabaseLogin];
GO
-- now refresh the Databases tree item in the Object Explorer connection made
-- using TestViewAnyDatabaseLogin
-- results: all databases are still in the list
----------------------------------------------------------------------------------
-- step 4: run this command as a sysadmin
USE [master]
GO
DENY VIEW ANY DATABASE TO [TestViewAnyDatabaseLogin];
GO
-- once again refresh the Databases tree item in the Object Explorer connection
-- made using TestViewAnyDatabaseLogin
-- results: no databases are in the list
I ran this against Standard Edition build 10.50.2500.0 as well as Enterprise Edition build 11.0.2325.0 with the same results.
If you experience different results from this code or other test code kindly share your work.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2012 at 7:30 pm
Apologies :blush: my bad. Thanks for posting up the SQL versions, because it just made me go back and check and I'd run up my code against a 2005 instance by mistake! So, for reference, the behaviour is actually different between 2005 and 2008.
September 9, 2012 at 7:41 pm
Crazy. Which build please because I get the same behavior on my 2005 Developer Edition instance build 9.00.5000.00.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 9, 2012 at 9:13 pm
Developer Edition 9.0.4266
September 10, 2012 at 6:43 am
MissTippsInOz (9/9/2012)
Developer Edition 9.0.4266
I just tried it on another machine using SSMS 2005 connecting to Developer Edition 9.0.4060 and I cannot reproduce what you're seeing. Are you using the repro code I posted?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 10, 2012 at 8:48 am
opc.three (9/10/2012)
MissTippsInOz (9/9/2012)
Developer Edition 9.0.4266I just tried it on another machine using SSMS 2005 connecting to Developer Edition 9.0.4060 and I cannot reproduce what you're seeing. Are you using the repro code I posted?
You know you have to check what server permissions have been granted to the [public] role, right? IIRC, the default setting is for the [public] server role to have VIEW ANY DATABASE, but if you've gone through any kind of security procedure or corporate policy/facet pushdown, this is often on the list of things that is automatically REVOKEd from [public].
And of course, if [public] has it, then everyone else inherits it, so you either have to REVOKE it from [public] or DENY it to specific users/groups. Which is what I suspect you guys are seeing...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2012 at 9:11 am
RBarryYoung (9/10/2012)
opc.three (9/10/2012)
MissTippsInOz (9/9/2012)
Developer Edition 9.0.4266I just tried it on another machine using SSMS 2005 connecting to Developer Edition 9.0.4060 and I cannot reproduce what you're seeing. Are you using the repro code I posted?
You know you have to check what server permissions have been granted to the [public] role, right? IIRC, the default setting is for the [public] server role to have VIEW ANY DATABASE, but if you've gone through any kind of security procedure or corporate policy/facet pushdown, this is often on the list of things that is automatically REVOKEd from [public].
And of course, if [public] has it, then everyone else inherits it, so you either have to REVOKE it from [public] or DENY it to specific users/groups. Which is what I suspect you guys are seeing...
public does have VIEW ANY DATABASE by default and my test instances do not undergo any changes to those permissions. REVOKE simply clears an explicit setting on the security entity targeted which I think is what you're getting at.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 10, 2012 at 10:06 am
opc.three (9/10/2012)
RBarryYoung (9/10/2012)
...public does have VIEW ANY DATABASE by default and my test instances do not undergo any changes to those permissions. REVOKE simply clears an explicit setting on the security entity targeted which I think is what you're getting at.
What I getting at is that many organizations have policies and practices (often automated) that will take that default permission away from [public]. And that alone would cause the differences in what you and Clare are seeing.
In other words, this is not a difference between SQL Server versions (AFAIK, this is the same on 2005, 2008, R2 and 2012), but rather in your enterprise environments' security policy and settings. i.e., on Clare's servers, someone/something has already taken VIEW ANY DATABASE away from the [public] role. Thus, DENY wasn't needed on her server, but it was on yours because yours still had the default permission for [public].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2012 at 10:17 am
RBarryYoung (9/10/2012)
opc.three (9/10/2012)
RBarryYoung (9/10/2012)
...public does have VIEW ANY DATABASE by default and my test instances do not undergo any changes to those permissions. REVOKE simply clears an explicit setting on the security entity targeted which I think is what you're getting at.
What I getting at is that many organizations have policies and practices (often automated) that will take that default permission away from [public]. And that alone would cause the differences in what you and Clare are seeing.
In other words, this is not a difference between SQL Server versions (AFAIK, this is the same on 2005, 2008, R2 and 2012), but rather in your enterprise environments' security policy and settings. i.e., on Clare's servers, someone/something has already taken VIEW ANY DATABASE away from the [public] role. Thus, DENY wasn't needed on her server
, but it was on yours because yours still had the default permission for [public].
I don't know about that. If it were denied on public in Clare's environment then she would not have seen the listing unless she explicitly added a GRANT to a login to view that specific database. In other words, REVOKE still would have no effect and in her testing REVOKE is having an effect.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 10, 2012 at 11:46 am
opc.three (9/10/2012)
...I don't know about that. If it were denied on public in Clare's environment then she would not have seen the listing unless she explicitly added a GRANT to a login to view that specific database. In other words, REVOKE still would have no effect and in her testing REVOKE is having an effect.
If REVOKE is having any effect, then it can only be because that login already had a GRANT for VIEW ANY DATABASE on it. All REVOKE does is to explicitly take away a previous explicit GRANT (it's essentially a "DROP GRANT" command).
So if you do a command like:
REVOKE {permission} FROM {user/login}
and it has any effect, then that can only be because there was a prior GRANT command that applied that same permission to that same user/login.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2012 at 12:14 pm
RBarryYoung (9/10/2012)
opc.three (9/10/2012)
...I don't know about that. If it were denied on public in Clare's environment then she would not have seen the listing unless she explicitly added a GRANT to a login to view that specific database. In other words, REVOKE still would have no effect and in her testing REVOKE is having an effect.
If REVOKE is having any effect, then it can only be because that login already had a GRANT for VIEW ANY DATABASE on it. All REVOKE does is to explicitly take away a previous explicit GRANT (it's essentially a "DROP GRANT" command).
So if you do a command like:
REVOKE {permission} FROM {user/login}
and it has any effect, then that can only be because there was a prior GRANT command that applied that same permission to that same user/login.
REVOKE also acts as a DROP DENY.
My test code assumes no prior GRANT. It creates a new database and new login and goes from there.
If public were denied then the test would go off-script as soon as the login was created and Step 2 were run as no databases would e in the list. Clare is claiming there was a database in the list and Step 3 (the REVOKE) removed it from the list. I cannot come up with a scenario, including non-standard public permissions, where that would be the case.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 10, 2012 at 4:34 pm
RBarryYoung (9/10/2012)
opc.three (9/10/2012)
RBarryYoung (9/10/2012)
...public does have VIEW ANY DATABASE by default and my test instances do not undergo any changes to those permissions. REVOKE simply clears an explicit setting on the security entity targeted which I think is what you're getting at.
What I getting at is that many organizations have policies and practices (often automated) that will take that default permission away from [public]. And that alone would cause the differences in what you and Clare are seeing.
In other words, this is not a difference between SQL Server versions (AFAIK, this is the same on 2005, 2008, R2 and 2012), but rather in your enterprise environments' security policy and settings. i.e., on Clare's servers, someone/something has already taken VIEW ANY DATABASE away from the [public] role. Thus, DENY wasn't needed on her server
, but it was on yours because yours still had the default permission for [public].
Absolutely correct Barry, my testing takes [public] out of the equation by revoking VIEW ANY DATABASE for [public] also. Apologies if I confused the issue with this, I should have mentioned it specifically (not yet an old enough hand at posting the truth, the whole truth and nothing but the truth).
Anyway that takes us back to no version differences, as you say.
September 10, 2012 at 5:22 pm
Phew, another mystery averted 😛
Thanks for posting the rest of the story Clare!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply