Re: SQL2005 Allow Login Limited Visibility

The problem is that 'viewing' any database is a server level permission, not
database-level.
And, iirc, the only way to control such 'viewing' is 'VIEW ANY DATABASE'
:-(.
But, if you can change owner of the database (not add user to db_owner
role!) to MyLogin, then this login will be able to see this DB in OE without
granted 'VIEW ANY DATABASE' right.

--
WBR, Evergray
--
Words mean nothing...


"pmattson" <pmattson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:12BBE855-D4B9-4B43-8CFF-DFB32CC433FB@xxxxxxxxxxxxxxxx
Thanks for the response.

I tried your suggestion to revoke view any database to public and to
revoke
anything I had set for MyLogin. I still get the problem that I can't see
the
"MyData" database with Object Explorer when logged in as "MyLogin".

Any additional ideas?

Thanks,
Phil Mattson

"Evergray" wrote:

You should revoke 'view any database' from public role in master
database.

use master

go


revoke view any database from public

also you should revoke permissions you've denied to MyLogin

REVOKE VIEW ANY DATABASE TO MyLogin

--
WBR, Evergray
--
Words mean nothing...


"pmattson" <pmattson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:43A028B3-E214-4785-B385-36F1BB915286@xxxxxxxxxxxxxxxx
Thanks for responding Kelly.

Actually this is with SQL2005. I feel I'm close but not quite there.
Any
ideas for doing this with SQL 2005?

Thanks,
Phil Mattson

"Andrew J. Kelly" wrote:

The real answer is to upgrade to SQL2005 where the security works as
you
would expect. There really isn't a way to do this properly in 2000 as
far
as
I know. Even though they can see the objects they don't have access
to
any
of the data.

--
Andrew J. Kelly SQL MVP


"pmattson" <pmattson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9BC1677C-0A7D-4AB1-A27B-5D21013379CC@xxxxxxxxxxxxxxxx
Please help!

I've got a SQL Login (MyLogin) which allows a the user to ONLY
access a
single database. The user is in the db_ddladmin, db_datareader, and
db_datawriter database roles to the single database which I'll call
database
"MyData" for this question.

If I connect with SQL Server Management Studio with login "MyLogin"
with a
new "Database Engine Query" or with "Object Explorer" I am able to
see
all
of
the databases, including database that I do NOT want the user to be
aware
of... simply allowing the user to see the other database names is a
security
problem for us.

With a little looking around I find that I can specifically disallow
this
specific user the ability to "view" databases. I execute the
following
commands as 'sa'.

USE master
DENY VIEW ANY DATABASE TO MyLogin

This achieves the desired result with the "Database Engine Query"
window.
When log into the server as "MyLogin" I am only able to see the
master,
tempdb, and "MyData" databases. However, when I connect with the
"Object
Explorer" as "MyLogin" I am only able to view the master and tempdb
system
databases.

My issue here is that I need the "MyLogin" user to be able to see
the
"MyData" database with Object Explorer so that they can add/edit
objects
with
the tool. They should not be able to see any other user databases.

I've tried the following with no luck:

1) Add "MyLogin" to the db_owner database role in the "MyData"
database.
2) Specifically grant "view definition" with:

use MyData
GO
GRANT VIEW DEFINITION to MyLogin

Anyone have the answer here? I've got to think there is a way to do
this.

Thanks,

Phil Mattson











.

Relevant Pages

  • Re: SQL2005 Allow Login Limited Visibility
    ... I tried your suggestion to revoke view any database to public and to revoke ... also you should revoke permissions you've denied to MyLogin ... "MyData" for this question. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL2005 Allow Login Limited Visibility
    ... You should revoke 'view any database' from public role in master database. ... REVOKE VIEW ANY DATABASE TO MyLogin ...
    (microsoft.public.sqlserver.security)
  • Re: SQL2005 Allow Login Limited Visibility
    ... And, iirc, the only way to control such 'viewing' is 'VIEW ANY DATABASE' ... I tried your suggestion to revoke view any database to public and to ... anything I had set for MyLogin. ... "MyData" for this question. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL2005 Allow Login Limited Visibility
    ... Kalen Delaney, SQL Server MVP ... Andrew J. Kelly SQL MVP ... I've got a SQL Login (MyLogin) which allows a the user to ONLY access ... db_datawriter database roles to the single database which I'll call ...
    (microsoft.public.sqlserver.security)
  • Re: SQL2005 Allow Login Limited Visibility
    ... Andrew J. Kelly SQL MVP ... db_datawriter database roles to the single database which I'll call ... "MyData" for this question. ... If I connect with SQL Server Management Studio with login "MyLogin" with a ...
    (microsoft.public.sqlserver.security)