Re: Revoke View any database from public in MSSQL 2005

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@xxxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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

"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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A446C59B-C7BA-4CBF-B158-D3F88BD24434@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: not creating tables as dbo anymore ?
    ... Database Owner (dbo) ... existing user ID in the database specified by database_name. ... with the login of the current connection. ...
    (microsoft.public.sqlserver.security)
  • Re: Change UDF Owner
    ... Does it exist in the master database? ... Users can be put in the dbo role, but dbo will still own objects ... concept of 'schema' as 'owner' rather than just schema as DDL is exploited ... to the appropriate Fixed Server Roles or whatever is applicable. ...
    (microsoft.public.sqlserver.security)
  • Re: conflicting object names in sql server 2000
    ... On the local database server when it does the select * from ... > michael.xxx Note that michael is the owner of the database. ... it will see if there is one owned by dbo. ...
    (microsoft.public.sqlserver.server)
  • Re: Confused about dbo
    ... Jasper Smith (SQL Server MVP) ... > database within a SQL Server instance. ... > There are several ways you could have the username dbo. ... > the true owner of the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Revoke View any database from public in MSSQL 2005
    ... Each user is the dbo to each database. ... view all the db's and logins on the server when using SMSE. ... I am running into an issue when I run the following query to revoke access ...
    (microsoft.public.sqlserver.security)