Only seeing dbs that have access to

Hi,
I came across a script to update the sp_MSdbuseraccess SP so that when a
user logs in to SQL Server 2000 using Enterprise Manager in SQL 2000 they
only see databases that they have access to
(http://support.microsoft.com/default.aspx/kb/889696). This works fine apart
from if users are using the new SQL Management Studio tool to connect to the
SQL 2000 server. Now they can see all databases again! Has anyone overcome
this issue?
Thanks - Kate.This is most probably not possible. SSMS doesn't use stored procedures when
doing things like
querying for what databases exist (*). And there isn't anything in the 2000
engine for this. There
is in 2005, where you by default (at the engine level) only see objects that
you actually have
access to (except for databases - you see all, but this is controllable thou
gh a REVOKE statement).
(*) I just ran a profiler trace to see the SQL statement submitted when expa
nding the databases
folder or the drop-down lost for a query windows. In both cases there was a
SELECT against a system
table. This was against a 2005 instance (I don't have a setup right now wher
e I can connect SSMS
against a 2000 instance), but I very much doubt it is any different.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl..
.
> Hi,
> I came across a script to update the sp_MSdbuseraccess SP so that when a u
ser logs in to SQL
> Server 2000 using Enterprise Manager in SQL 2000 they only see databases t
hat they have access to
> (http://support.microsoft.com/default.aspx/kb/889696). This works fine apa
rt from if users are
> using the new SQL Management Studio tool to connect to the SQL 2000 server
. Now they can see all
> databases again! Has anyone overcome this issue?
> Thanks - Kate.|||Ok great - what was the SQL statement?
thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>|||you mention a REVOKE statement for SQL 2005 installs - could you elaborate
on this?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
> This is most probably not possible. SSMS doesn't use stored procedures
> when doing things like querying for what databases exist (*). And there
> isn't anything in the 2000 engine for this. There is in 2005, where you by
> default (at the engine level) only see objects that you actually have
> access to (except for databases - you see all, but this is controllable
> though a REVOKE statement).
> (*) I just ran a profiler trace to see the SQL statement submitted when
> expanding the databases folder or the drop-down lost for a query windows.
> In both cases there was a SELECT against a system table. This was against
> a 2005 instance (I don't have a setup right now where I can connect SSMS
> against a 2000 instance), but I very much doubt it is any different.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kate" <Kate_w112@.hotmail.com> wrote in message
> news:eHC6zgc3HHA.2064@.TK2MSFTNGP03.phx.gbl...
>|||Basically a SELECT from sys.databases. Remember, I was running against a 200
5 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELE
CT from sysdatabases).
But you can easily check this yourself using the same method I did (using Pr
ofiler).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:eLAEWnf3HHA.464@.TK2MSFTNGP02.phx.gbl...

> Ok great - what was the SQL statement?
> thanks.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>|||Check out the "VIEW ANY DATABASE" privilege. Try below, for example:
CREATE LOGIN x WITH PASSWORD = 'hjus8#."3'
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --14 rows
REVERT
DENY VIEW ANY DATABASE TO x
EXECUTE AS LOGIN = 'x'
SELECT name FROM sys.databases --2 rows
REVERT
GO
DROP LOGIN x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kate" <Kate_w112@.hotmail.com> wrote in message news:Oj0C6nf3HHA.5796@.TK2MSFTNGP05.phx.gbl..
.
> you mention a REVOKE statement for SQL 2005 installs - could you elaborate
> on this?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>