Systems Engineering and RDBMS

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:

USE MASTER
GO
DENY VIEW ANY DATABASE TO TEST;
GO

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:

perm_1

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.

Resources:

  • MSFT connect post – here.
  • SQL-Server-Performance Q&A post – here.
  • BOL – View Any Database server permission – here.

4 Responses to “Allowing users to view only the databases that they have access to”

  1. Hello,
    We can allow the user to see just the database that we want him to see following the steps below:
    Firts, execute the “DENY VIEW ANY DATABASE TO ” command, just like decipherinfosys wrote in the post above.
    Second, we have to set the user as owner of the database. To do that, in the SSMS right-click the database that you want to grant access to the user and select Properties. In the DataBase Properties dialog box that appears select Files. Finally, in the Owner text box field, select the user.
    That´s it! At his next login he´ll see just that database in his SSMS Object Explorer.
    What is really sad here is that the we can do that only to users that we define as the databases owners!
    Regards,
    Allison

    • Anonymous said

      Hey Allison,

      You’re right and thank you for providing this reponse🙂

      The complaint most administrators have with this option is that it doesn’t scale to multiple logins. If a DBA wants to allow to logins to see the same database after “VIEW ANY DATABASE” has been revoked from public, she can’t because a database can’t have two owners. Also, DBAs don’t want (in all cases) to have a login that can see a database also have owner rights to it.

      This was poor forethought by the SQL Server team. Adding some sort of functionality to allow users to view specific databases would’ve been helpful.

      Crazy Adam

  2. Anonymous said

    It might be helpful to determine why this does not occur in Visual Studio 2010. The VS2010 server explorer only shows a specific database, while SSMS 2008 shows all databases. It seems obvious that SSMS 2008 desperately needs some kind of “database filter” to avoid the very long delay we are experiencing when we try to connect to a specific hosted sql server 2008 database.

  3. […] from:https://decipherinfosys.wordpress.com/2009/03/23/allowing-users-to-view-only-the-databases-that-they-… […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: