I was at the Microsoft Hosting Summit last week, meeting with both Service Providers and our internal Hosting Evangelists. One of the most common questions that comes up in these particular environments is how to hide DBs that users do not have access to. The answers are different in the case of SQL 2000 and SQL 2005.
Enterprise Manager uses DMO for the majority of enumerations, DMO uses a mix of generated SQL queries and serverside SPs to provide the source of this. QA uses direct queries and in some cases shares SPs with DMO.
This KB article (despite its title and goal) provides a way to hide databases in EM and any other app that uses DMO and its SPs ( including ISV apps), but select from sysdatabases will obviously bypass this.
There is both good and not so good news about SQL Server 2005. First the good, we added a huge number of new more granular permissions, several of which are around metadata. This means there is a fix to address this problem from the server that means all clients are fixed. The not so good is that this is not on by default, the second not so good info is that SSMS does not use server side SPs for the most part and hence when using SSMS against SQL 2000 the "hidden" dbs from the above example will re-appear, obviously SSMS respects the server side permissions in SQL Server 2005. The magic permission is view any database.
Can't find a KB on this for SQL 2005 so take the script from below and paste into your favourite query editor, and enjoy.
--Check our work
--Only master, tempdb and foo
--Only master, tempdb, no FOO?!
--Now let’s grant view definition to user2
--Now we can see Foo
(This post was brought to you through the wonders of modern scheduling technology as Euan is actually relaxing in Vegas, normal service such as the approval of comments and the deleting of spam will commence on his return)