Re: How to hide unauthorized databases with SQL 2005?
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 25 Apr 2006 07:00:41 -0500
After VIEW ANY DATABASE is denied, only master, tempdb, and databases that
the login owns are visible. Other databases that the user can access are
not enumerated but can still be accessed directly by setting the database
context (e.g. USE). Unfortunately, SSMS Object Explorer functionality is
limited to visible databases.
The reason for this behavior is that it is necessary to open each database
on the server to determine whether or not a non-privileged login has
database access. This caused performance issues on servers with a lot
(100's) of databases.
If this feature is important to you, make a suggestion (or vote on the
importance if already submitted) at the product feedback center:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@xxxxxxxxxxx> wrote in message
news:eV8jH9BaGHA.4116@xxxxxxxxxxxxxxxxxxxxxxx
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one database
while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%2300soIBaGHA.3304@xxxxxxxxxxxxxxxxxxxxxxx
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@xxxxxxxxxxx> wrote in message
news:e50gEfAaGHA.3704@xxxxxxxxxxxxxxxxxxxxxxx
> Hi,
>
> I have configure permission for userA and he can access only one
database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
>
> Appreciate all your reply.
>
> Shane
>
.
- References:
- How to hide unauthorized databases with SQL 2005?
- From: SL Coder
- Re: How to hide unauthorized databases with SQL 2005?
- From: Dan Guzman
- Re: How to hide unauthorized databases with SQL 2005?
- From: SL Coder
- How to hide unauthorized databases with SQL 2005?
- Prev by Date: Windows Domain Login
- Next by Date: Re: public role question
- Previous by thread: Re: How to hide unauthorized databases with SQL 2005?
- Next by thread: Windows Domain Login
- Index(es):
Relevant Pages
|
|
