Tuesday, December 12, 2017

Hide SQL server databases which user don't have rights


Part of DevOps implementation we are now delegating rights for DevOps teams by following least privilege principle.

That why I also started to look how to delegate SQL permissions which will allow users only see databases which they have access.

Default settings on SQL server

By public role has right called for "VIEW ANY DATABASE". It can be removed using this SQL query:

but problem with that solution is that then users cannot see even these databases which they have access unless you set user as dbo.

Here is list of my test databases when I look them as sa

And here is what test user will see. User have db_datareader role on ShouldBeSeenByTestUser1 and db_owner role on ShouldBeSeenByTestUser2 database.

Note that if user know name of each database where he has access he also can connect to them.

Maybe Microsoft will fix this issue?

This issue has been reported to Microsoft and they are actually "promised" to fix in on future version of SQL server. There is just small BUT they have promised that on year 2008 and they have not still implemented anything to this one...

So let Olli make it working

Because there is no official solution offered by Microsoft it is time to look if Olli can find some "good enough" solution to it.

How SQL Server Management Studio is looking for DBs?

To be able to figure out solution/workaround to this issue I needed to first reverse engine logic which is used to get list of databases to SQL Server Management Studio.

I did that by running SQL Profiler same time when I connected to instance with SQL Server Management Studio.

Simplified version of query looks like this:
SELECT * FROM master.sys.databases

Using this query I was able to see content of that sys.databases view:
select object_definition(object_id('[sys].[databases]')) AS [processing-instruction(x)] FOR XML PATH('')

Simplified version of it looks like this:
SELECT * FROM sys.sysdbreg WHERE has_access('DB', id) = 1 

It gets list of databases from system table sys.sysdbreg and uses non-documented  has_access function to filter out databases.

It is possible to run queries against of sys.sysdbreg table by using dedicated admin connection but has_access function is not accessible even from there.

Creating stored procedure which lists only databases where user has access

There is actually couple of ways to get lists of databases where user have access but this is simplest one which I found.

First we create stored procedure which will list all databases. That contains command WITH EXECUTE AS OWNER so it will be run by using master database owner rights. In this example it is sa.
SELECT name FROM sys.databases

Then we will create another stored procedure which will be run using user rights and where we filter databases which user have access:
CREATE TABLE #databases (
 name sysname not null
INSERT INTO #databases
EXEC sp_all_dbs
SELECT name FROM #databases WHERE HAS_DBACCESS(name) = 1

and last step is grant role public to run that stored procedure:

So now user can use this stored procedure to see which databases he actually have access:

and because we are using HAS_DBACCESS function we get list of all databases where user have at least public role.

But how to get that to SQL Server Management Studio?

Unfortunately I don't have access to SQL Server Management Studio source code (because I'm not working on Microsoft) so I cannot fix this issue to it.

But there is new open source solution called for SQL Operations Studio which also uses sys.databases view to get list of databases and because it is open source I was able to make modified version of it.

You can see my code change and download modified DLL from here. Just download 0.23.6 version of SQL Operations Studio and drop that MicrosoftSqlToolsServiceLayer.dll to \resources\app\extensions\mssql\sqltoolsservice\Windows\1.2.0-alpha.37\ folder over existing one.

And here is example how it looks then. Notice that on left side you can still see only system databases (because these are queried using same way that on SSMS) but on right side you can actually see these databases which was hidden without this modification.

Closing words

I will paste this blog post as comment to that bug report which I mentioned earlier and hope that some day Microsoft will include fix to SQL server. My opinion is that this should be fixed to sys.databases view.

In mean while my solution can be used as workaround.