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.

SQL 2000

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.

SQL 2005

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.

 

use master

go

create database foo

go

use foo

go

create login login1 with password = 'foo', check_policy = off

go

use foo

go

sp_changedbowner 'login1'

go

use master

go

deny VIEW any DATABASE to login1

go

--Check our work

use master

go

execute as login ='login1'

go

select * from sys.databases

--Only master, tempdb and foo

revert

go

use foo

go

create login login2 with password = 'foo', check_policy = off

go

create user user2 for login login2 with default_schema=dbo

go

exec sp_addrolemember 'db_owner', 'user2'

go

use master

go

deny VIEW any DATABASE to login2

go

execute as login ='login2'

go

select * from sys.databases

--Only master, tempdb, no FOO?!

revert

--Now let’s grant view definition to user2

use foo

GO

grant view definition to user2

GO

execute as login ='login2'

go

select * from sys.databases

--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)