Re: SQL2005 Allow Login Limited Visibility

Sorry I spaced on that part. What EverGray states is right but you may want
to have a look here:

http://www.microsoft.com/technet/technetmag/issues/2006/01/ProtectMetaData/default.aspx


--
Andrew J. Kelly SQL MVP


"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: Performance really poor if calling data from sql clients
    ... Andrew J. Kelly SQL MVP ... The TCP connection is running from the client ... On a computer running the test which is not the database hosting ...
    (microsoft.public.sqlserver.clients)
  • Re: Fill Factor
    ... Well the only way it would go back to 90 is if someone or something is reindexing with a different fill factor. ... Andrew J. Kelly SQL MVP ... > database, i deployed a database to a client and sit the Fill Factor for ...
    (microsoft.public.sqlserver.setup)
  • Re: DataBase
    ... Andrew J. Kelly SQL MVP "Andrew J. Kelly" wrote in message ... Sorry I don't know anything about> dbase being supported with windows other than there are odbc and possibly> oledb drivers for it now, ... What I actually need to>> know>>>> is it posible to create this as a database and create tables in it, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: different environment fails to fill a dataset
    ... Are you sure that the login on the Test system has the database Planit as ... Andrew J. Kelly SQL MVP ... on the test workstation but not my development station. ...
    (microsoft.public.sqlserver.clients)
  • Re: SQL2005 Allow Login Limited Visibility
    ... Andrew J. Kelly SQL MVP ... db_datawriter database roles to the single database which I'll call ... If I connect with SQL Server Management Studio with login "MyLogin" ...
    (microsoft.public.sqlserver.security)