Wednesday, November 12, 2008

Can I hide undesirable database in SSMS?

We have discussed the issue many times and there is an opened connection to Microsoft to add this feature in the next release. Recently I was visiting our client and we tried to do something for the subject.

1) CREATE new SQL login 'John'
2) CREATE a user named 'John in master database
3) GRANT CREATE DATABASE to John
4) While impersonating John, create a database called 'demo'
5) REVOKE CREATE DATABASE permission from John
6) REVOKE VIEW ANY DATABASE permission from PUBLIC
7) Register this server as John
8) From the 'John' session, expand database tree. Now, you should see only master, tempdb, dbtest
9) GRANT VIEW ANY DATABASE to PUBLIC
10) From the 'John' session, you should see all the databases.

However, this works perfectly if the user is the owner of the database.