Allowing users to view only the databases that they have access to
Posted by decipherinfosys on March 23, 2009
At times, even the simplest of questions can leave one dumbfounded. An end user asked such a question to me recently: “I have many logins in my environment and to the sales folks who use a common login, when they connect through SSMS, I do not want them to be able to see all the other databases on the instance. They have access to only one database and that is the only one that they should see in object explorer. How can we achieve this?” Pretty simple question, isn’t it?
Well, I thought that all that I would need to do is look up BOL to see what specific permission do I need to DENY to that login. We are using SQL Server 2008 here. Upon looking up BOL, it seems like I can use the “DENY VIEW ANY DATABASE TO <login name here>” command. So, I go ahead and execute it in the context of the master database:
DENY VIEW ANY DATABASE TO TEST;
Here test is the login that I created. Now, this login does have db_datareader persmissions granted on a user database called: DECIPHERTEST. Let’s see what we will see in the object explorer after we have executed the DENY command from above. The image below shows two connections in the object explorer – the first one is for a login that does not have the DENY command executed against it and the second connection below it is for the test login:
As you can see, for the test login – now we do not see any user databases at all even though this login does have data reader privileges on one of the user databases – infact, in the list of the system databases also, it sees only master and tempdb databases. Model and Msdb are not visible. But that is not what our intent was. We wanted the end users to be able to see the databases that they had access to and in this case, DECIPHERTEST should have shown up in their list. Next, I traced out what the object explorer was reading in order to display that view but did not find any permissions checks that I could toggle the values for.
Then I googled it and came across this post from the year 2006 in which another user had posted it on MSFT connect – the post explains the same issue along with script examples that would help anyone reproduce the issue. The issue is still present in SQL Server 2008. And as this Q&A on sql-server-performance.com points out, the permission of “View any database” is granted by default to the public role and as a result of that, every login can see all the databases on a given instance. And if the permission is denied, then there is no current way of allowing the end users to be able to see their user databases. As noted in the MSFT connect post, this will more than likely be a feature in a future version.