Share via

FAQ: How do I prevent users from seeing DBs in Enterprise Manager/SSMS that they don't have rights to?

  • Article

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)

Comments