Re: SQL2005 Allow Login Limited Visibility


Yeah, thanks! ;-)
I hadn't been able to find a link to this, although I have the printed copy
of TechNet Magazine.

--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com


"Andrew J. Kelly" <sqlmvpnooospam@xxxxxxxxxxxx> wrote in message
news:OOis7P7NGHA.3264@xxxxxxxxxxxxxxxxxxxxxxx

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: CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to
    ... Go to the Database tab and click on the browse button next to the connection string. ... In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate. ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • CREATE AGGREGATE failed because type Concatenate does not conform to UDAGG specification due to meth
    ... Now register the assembly and the aggregate in the SQL Server database you want ... I'm trying to do some CLR integration with sql server 2005. ...
    (microsoft.public.sqlserver.programming)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)