FAQ: How do I prevent users from seeing DBs in Enterprise Manager/SSMS that they don’t have rights to?


I was at the Microsoft Hosting Summit last week, meeting with both Service Providers and our internal Hosting Evangelists. One of the most common questions that comes up in these particular environments is how to hide DBs that users do not have access to. The answers are different in the case of SQL 2000 and SQL 2005.


SQL 2000


Enterprise Manager uses DMO for the majority of enumerations, DMO uses a mix of generated SQL queries and serverside SPs to provide the source of this. QA uses direct queries and in some cases shares SPs with DMO.


This KB article (despite its title and goal) provides a way to hide databases in EM and any other app that uses DMO and its SPs ( including ISV apps), but select from sysdatabases will obviously bypass this.


SQL 2005


There is both good and not so good news about SQL Server 2005. First the good, we added a huge number of new more granular permissions, several of which are around metadata. This means there is a fix to address this problem from the server that means all clients are fixed. The not so good is that this is not on by default, the second not so good info is that SSMS does not use server side SPs for the most part and hence when using SSMS against SQL 2000 the “hidden” dbs from the above example will re-appear, obviously SSMS respects the server side permissions in SQL Server 2005. The magic permission is view any database.


Can’t find a KB on this for SQL 2005 so take the script from below and paste into your favourite query editor, and enjoy.


 


use master


go


create database foo


go


use foo


go


create login login1 with password = ‘foo’, check_policy = off


go


use foo


go


sp_changedbowner ‘login1’


go


use master


go


deny VIEW any DATABASE to login1


go


–Check our work


use master


go


execute as login =‘login1’


go


select * from sys.databases


–Only master, tempdb and foo


revert


go


use foo


go


create login login2 with password = ‘foo’, check_policy = off


go


create user user2 for login login2 with default_schema=dbo


go


exec sp_addrolemember ‘db_owner’, ‘user2’


go


use master


go


deny VIEW any DATABASE to login2


go


execute as login =‘login2’


go


select * from sys.databases


–Only master, tempdb, no FOO?!


revert


–Now let’s grant view definition to user2


use foo


GO


grant view definition to user2


GO


execute as login =‘login2’


go


select * from sys.databases


–Now we can see Foo


(This post was brought to you through the wonders of modern scheduling technology as Euan is actually relaxing in Vegas, normal service such as the approval of comments and the deleting of spam will commence on his return)

Comments (6)

  1. Sivashankar says:

    Its realy a fantastic work….!!!!!!!

  2. I came across this excellent blog by Euan Garden, who is a Group Program Manager at Microsoft.

    http://blogs.msdn.com/euanga/archive/2006/05/04/585513.aspx

  3. SQL Server 9.0.2047

    The database ‘foo’ shows up for login2 when execution context is changed using EXECUTE AS. However, if you login to Query Window or Object Explorer using login2, the database ‘foo’ does not show up.

    After granting VIEW DEFINITION to user2, if you connect to Query Window or Object Explorer using login2, database foo does not show up.

    When connected to query window using login2, initially the database foo does not show up in Available Databases list. But "use foo" works, and database then shows up in Available Databases combo on top. And then sys.databases returns ‘foo’.

  4. kevin.bailey says:

    The way around getting foo to show up after the deny view any database to login2 is:

     1)  log in as sa and delete the database foo.

     2)  while logged in as sa, grant user login2 create database permission.

     3)  Grant all permissions to master and tempdb to user login2.

     4)  log out of sa.

     5)  log in as login2 user.

     6)  create table foo while logged in as login2 and set login2 as table owner.

    Now foo will show up as the only database, except master and tempdb (which you want anyway).