Revoke View any database from public in MSSQL 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carlos Caneja - 26 Sep 2006 19:22 GMT

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

Reply to this Message

Arnie Rowland - 26 Sep 2006 20:55 GMT

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.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

> Hello Everyone,
>
[quoted text clipped - 13 lines]
>
> -Carlos

Reply to this Message

Carlos Caneja - 26 Sep 2006 21:36 GMT

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/archive/2006/04/17/443118.aspx

Thanks for the reply,

-Carlos

> Doesn't it make sense, after all, you removed (REVOKE) the ability to see
> (VIEW) any (that means ALL) database to everyone (PUBLIC).
[quoted text clipped - 22 lines]
> >
> > -Carlos

Reply to this Message

Sue Hoegemeier - 27 Sep 2006 15:39 GMT

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

>Arnie,
>
[quoted text clipped - 43 lines]
>> >
>> > -Carlos

Reply to this Message

Carlos Caneja - 27 Sep 2006 19:01 GMT

Sue,

Each user is the dbo to each database.

-Carlos

> You mention they should see the databases "since their role
> to each is "DBO".
[quoted text clipped - 57 lines]
> >> >
> >> > -Carlos

Reply to this Message

Sue Hoegemeier - 27 Sep 2006 19:22 GMT

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

>Sue,
>
[quoted text clipped - 63 lines]
>> >> >
>> >> > -Carlos

Reply to this Message