Only seeing dbs that have access to

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...
> 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.
Basically a SELECT from sys.databases. Remember, I was running against a 2005 instance, so you won't
see the same SELECT against a 2000 instance (you will probably see some SELECT from sysdatabases).
But you can easily check this yourself using the same method I did (using Profiler).
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 in 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 in
> message news:Ozw%23nrd3HHA.2752@.TK2MSFTNGP06.phx.gbl...
>