Hiding Databases From Unauthorised Users When Using ESRI MapIt

I have been getting a dedicated ESRI MapIt server up and running today and I encountered an interesting problem this afternoon.

Basically I needed the Spatial Data Services to point to our Technical Solutions Team main database server. This is great and all and actually very simple to do. You specify a username and password and SDS will connect to the database when requested and you can control which databases the user has access to via Microsoft SQL Server 2008.

So what’s the problem? Well like Microsoft SQL Server Management Studio, whenever a user logs in the server, regardless of whether they have permission to access a database or not, they still see ALL of the databases on the server.

Ok, you might ask why would I want to do this? Simply because a number of the databases on our main database server are confidential and while the user might not be able to access them, they can still see the names and this still plays a part in the confidentiality.

In this case I created a database user called MapIt. The user does not have access any databases. I’ve logged in with the MapIt user using MSSMS. As you can see from the screenshot below ALL of the databases on the server are visible to the user. While the user won’t be able to access them, they can still see them. Some databases have been removed from the screenshot due to confidentiality reasons.

As such when you access the SDS in MapIt, you see the similar thing.

So I have been looking for ways to work around this problem as I would still like to use our main database server. What I found was that SQL Server 2008 still is not capable of doing this easily. There are a few workarounds but they are not ideal. Based on an article that I found:

Until SQL Server version 2000 it was not possible to hide the database information from being displayed on SQL Server Enterprise Manager. In SQL Server 2005 it is possible with a new server side role that has been created. VIEW ANY DATABASE permission is a new, server-level permission. A login that is granted with this permission can see metadata that describes all databases, regardless of whether the login owns or can actually use a particular database. Please note By default, the VIEW ANY DATABASE permission is granted to the public role. Therefore, by default, every user that connects to an instance of SQL Server 2005 can see all databases in the instance.

Quickly looking up the VIEW ANY DATABASE permission on MSDN: http://msdn.microsoft.com/en-us/… shows us how to apply this. So I logged in with sa user and ran the following SQL:

DENY VIEW ANY DATABASE TO MapIt;

What effect did this have on MapIt user now? Now when I log into MSSMS with the MapIt user I see only the one database.

 

If we have a look at the SDS on MapIt we see only the one database. Great! This looks like it’s worked BUT it’s only worked because the sandbox database we are seeing was created by the MapIt user in SQL Server 2008. Added to this the MapIt user is the database owner.

An interesting point to note is that being a member of db_owner is not sufficient to see the database if "view any database" was denied. In this regard SQL Server Development team are working on new features in order to make this more affective in future releases.

So this is still a problem with SQL Server 2008 and while this particular scenario might only apply to a small fraction of users using MapIt, it can become a pain. I guess the next version of Microsoft SQL Server could address this.

The original article I found that helped me can be found here: http://www.sql-server-performance.com/faq/…