Revoke View any database from public in MSSQL 2005

Hello Everyone,
I am running into an issue when I run the following query to revoke access
to all the SQL 2005 users from other DB.s
use master
Revoke View any database from public
Go
When the users logs in with SMSE he/she does not see the db under “databas
es”.
They can only access via QUERY.
Thanks,
-CarlosDoesn't it make sense, after all, you removed (REVOKE) the ability to see
(VIEW) any (that means ALL) database to everyone (PUBLIC).
Perhaps you had a different outcome in mind, and you would like some help.
But you're going to have to be more specific, It's a waste of time to try to
guess what you want as a final outcome.
Your question/request is not clear.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
> Hello Everyone,
> I am running into an issue when I run the following query to revoke access
> to all the SQL 2005 users from other DB.s
> use master
> Revoke View any database from public
> Go
> When the users logs in with SMSE he/she does not see the db under
> "databases".
> They can only access via QUERY.
> Thanks,
> -Carlos
>|||Arnie,
Sorry for not being so clear on this. I will try to explaing in a different
way.
We run a shared hosting enviroment and SQL 2005 out of the box let's users
view all the db's and logins on the server when using SMSE.
By revoking the view command the users should still be able to see their
DB's since their role to each is "DBO".
As I said on the post, they can run queriues against the db using SMSE but
the tool will not graphicaly show you the db as it shows Master and tempdb.
Perhaps this will also help you understand my issue a big better.
http://weblogs.asp.net/hosterposter.../17/443118.aspx
Thanks for the reply,
-Carlos
"Arnie Rowland" wrote:

> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
> Perhaps you had a different outcome in mind, and you would like some help.
> But you're going to have to be more specific, It's a waste of time to try
to
> guess what you want as a final outcome.
> Your question/request is not clear.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Carlos Caneja" <Carlos Caneja@.discussions.microsoft.com> wrote in message
> news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@.microsoft.com...
>
>|||You mention they should see the databases "since their role
to each is "DBO".
The behavior will work as the article describes but I'm not
sure what you mean by their role to each is dbo. If you mean
adding the user to db_owner role, that won't do it. The user
needs to be mapped to dbo - as in be the owner of the
database. There is a difference.
Look at who the owner of the database is by executing
sp_helpdb 'YourDatabase'
or execute
sp_helpuser 'dbo'
-Sue
On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Arnie,
>Sorry for not being so clear on this. I will try to explaing in a differen
t
>way.
>We run a shared hosting enviroment and SQL 2005 out of the box let's users
>view all the db's and logins on the server when using SMSE.
>By revoking the view command the users should still be able to see their
>DB's since their role to each is "DBO".
>As I said on the post, they can run queriues against the db using SMSE but
>the tool will not graphicaly show you the db as it shows Master and tempdb.
>Perhaps this will also help you understand my issue a big better.
>http://weblogs.asp.net/hosterposter.../17/443118.aspx
>Thanks for the reply,
>-Carlos
>"Arnie Rowland" wrote:
>|||Sue,
Each user is the dbo to each database.
-Carlos
"Sue Hoegemeier" wrote:

> You mention they should see the databases "since their role
> to each is "DBO".
> The behavior will work as the article describes but I'm not
> sure what you mean by their role to each is dbo. If you mean
> adding the user to db_owner role, that won't do it. The user
> needs to be mapped to dbo - as in be the owner of the
> database. There is a difference.
> Look at who the owner of the database is by executing
> sp_helpdb 'YourDatabase'
> or execute
> sp_helpuser 'dbo'
> -Sue
> On Tue, 26 Sep 2006 13:36:02 -0700, Carlos Caneja
> <CarlosCaneja@.discussions.microsoft.com> wrote:
>
>|||Lines: 1
X-Newsreader: Forte Agent 2.0/32.652
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: c-24-8-112-204.hsd1.co.comcast.net 24.8.112.204
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:1263
DBO gets misunderstood sometimes so that's why I'm wondering
if that's not the issue. I just did a few tests and it works
fine on two instances I just tested in on. As long as the
logins were database owners, they could see the database.
Anything else, and they could not see the database.
If SomeUser is the login and user in question, the results
for sp_helpuser 'dbo' in that database are:
UserName: dbo
GroupName: db_owner
LoginName: SomeUser
The results for sp_helpdb 'YourDatabase list the owner as
SomeUser.
If that's your results, try changing the database owner
using sp_changedbowner and try changing it back to the user
you need to have own the database.
-Sue
On Wed, 27 Sep 2006 11:01:02 -0700, Carlos Caneja
<CarlosCaneja@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Each user is the dbo to each database.
>-Carlos
>"Sue Hoegemeier" wrote:
>