How do I prevent users from seeing DBs in Enterprise Manager/SSMS that
they don't have rights to?
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
Thanks to MSDN Blogs for this entry!!
