One of our customers is implementing a very large consolidation project in which they enable users to create their own databases through an automatic web interface. Hey, don’t panic, it’s for development environment only!
The problem is that after the databases are created we want the users to see only the databases they created, and not all the databases on the server. By default, every login can see all the databases on the server.
In order to block this option we ran the following script:
PgSQL
DENY VIEW ANY DATABASE TO [LoginName]
1
DENY VIEW ANY DATABASE TO [LoginName]
But now the login can’t see any database in SSMS, even if it has db_owner permissions on the database – the highest permission possible on a DB !
You would expect to have a script like:
PgSQL
GRANT VIEW MyDB TO [LoginName]
1
GRANT VIEW MyDB TO [LoginName]
Well, don’t look for it, cause it doesn’t exist. The only solution is to change the owner of the database (don’t confuse with db_owner permissions) to the login:
PgSQL
EXEC dbo.sp_changedbowner @loginame = N'LoginName'
1
EXEC dbo.sp_changedbowner @loginame = N'LoginName'
Now, our Login will be able to see only the databases he owns. The downside of this solution is that only one login per DB will be able to have this configuration, but remember that a login can actually be a windows group and hence implicate many users.
By the way, there’s a connect open on this issue, please vote so Microsoft gives a better solution!
Enjoy !