Hide database names from unauthroized users in SSMS


  • If a user is not authorized to see a database can I exclude that database from even appearing in Management Studio for that user







    Tuesday, September 26, 2006 5:32 PM


All replies

  • Hi,

    you would need to revove the permission 'VIEW ANY DATABASE' from the role PUBLIC.

    HTH, Jens K. Suessmeyer.

    Wednesday, September 27, 2006 6:37 AM
  • I found the server-level  permission "View any database", denied it to the test user, and found exactly that.  The user couldn't see (under SSMS) any databases whatsoever including the one he is the DBO of! How can I assign him permissions to see that one database but no others in the SSMS tree?





    P.S. The evidence is if I removed that permission from "public" then no one -- maybe even the sa? - could see any databases in SSMS.!




    Thursday, October 05, 2006 2:54 PM
  • How are you making the user an owner? Are you making him a member of db_owner or are you using the alter authorization statement to make the user the actual owner of the database?

    You can tell by looking at the owner_sid in sys.databases - the sid should point to the owning login. If the login is the owner of the database, denying that user  view any database will not prevent him from viewing the database in sys.databases or Management Studio.

    Jack Richins
    SDE Sql Server

    Thursday, October 05, 2006 7:15 PM
  • I made him a member of the db_owner.

    When I run

    sp_helpuser <DBName>

    it returns x01 as the SID for the username dbo.

    When I run

    use DBName

    select * from sys.databases

    it returns.......


    for the owner sid of the database.


    So the user is not the DBO (though he belongs to that role).

    Then back to my original question. How can I deny a user from seeing any databses in the SSMS tree that he does not have permission to access?







    Thursday, October 05, 2006 8:28 PM
  • DENY VIEW ANY DATABASE to the test login. This prevents the user from seeing any databases for which the user is not the DBO. You can make the user a DBO by using ALTER AUTHORIZATION DATABASE::<database> TO <login>. Being a member of db_owner is not sufficient to see the database if "view any database" was denied.

    This is not very clean and may not work for you if you can't make this user login the actual DBO of the database you want him to administer or have multiple logins you want to secure in this manner as only a single login can be the actual DBO of a database.

    This is something we're investigating for future improvements. So if you could provide some details on what you would like this login to be able to do and what you don't want the login to see or do, it could help us better solve your problems as we design future versions of SQL Server. Also whether you use SQL Logins or Windows Integrated Logins - how many different logins you'd like to secure in this way, and anything else you think might be relevant.

    Jack Richins
    SDE SQL Server

    Thursday, October 05, 2006 8:51 PM
  • Dear Jack,

    This whole issue took me by surprise. In our shop we have three teams of developers and all the development datbases are in a single sql instance. One of the group leaders asked me if there is any way to prevent those not in his group from even seeing the database display name under SSMS and sql tools (e.g. query editor, via OLEDB).   I don't know the reason for his concern  -- developers outside his group are not authorized to even view his databases but from a security perspective it seems reasonable: If a user does not have access to a databse, why should the database even "exist" for that user. This applies regardless of the form of Windows authentication used.




    Thursday, October 05, 2006 10:34 PM
  • Thanks, this isn't a scenario we'd been discussing so I'm glad you brought this up. I'm sorry we don't have a solution for you now, but hopefully in the future we can do something to enable this.

    Jack Richins
    SDE SQL Server

    Thursday, October 05, 2006 10:49 PM
  • Although it blocks users from seeing other databases in the SMSS database dropdown, as the user I can still query sys.databases and see everything and I have used the deny view any database to public. In a hosting environment, this just won't be acceptable. In sql 2000, I just created a sysdatabases view that looked at your credentials and passed what you had rights to. Can't do that in 2005, but definately need this.
    Wednesday, November 29, 2006 12:56 AM
  • The main issue here is that determining what databases a user can see is an expensive check, as the permissions to access the database are stored in the database itself, so verifying them requires a database access. This prevents a general solution from being available through SSMS - some customers manage hundreds of databases on a single server, which would make such checks prohibitive.

    You can still write a view in SQL Server 2005 that could filter rows using "has_dbaccess() = 1" as a predicate, but you should only do this if you have a limited number of databases, as it would be too expensive otherwise.

    I also want to add that we're aware of this request and we're looking at ways to address it.


    Thursday, November 30, 2006 9:50 PM
  • I have tried changing the view any database property, which works, but the user cannot see the databases that they own... is there something else that needs to be done for them to see these databases and no others?


    Tuesday, September 11, 2007 6:20 PM
  • This is what I ended up doing:


    --SQL 2005

    If @@version like 'Microsoft SQL Server 2005%'

    Begin -- Create Login only if it doesn't exist:

    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = @Username)


    Set @dsql ='create login ' + @Username + ' with password = N'''+ REPLACE(@password, '''', '''''') + ''', DEFAULT_DATABASE=' + QUOTENAME(@DBName) + ', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'

    execute (@dsql)

    set @dsql = 'use master; GRANT CONNECT SQL TO ' + QUOTENAME(@DBName)

    execute( @dsql )


    --Deny ability to see anything other than master, tempdb and user database.

    Set @dsql = 'use master; deny VIEW any DATABASE to ' + @Username

    --Print @dsql

    execute (@dsql)



    --Add user as dbo to database.

    Set @dsql = @DBName + '.dbo.sp_changedbowner ' + @Username

    --Print @dsql

    execute (@dsql)


    But, I don't think you can grant multiple user's to DBO, so you will only have one user that can see master, temp, and their database.


    -Chuck Lathrope

    Tuesday, September 11, 2007 6:35 PM
  • Hi There,

    Just found this thread and while trying to help a friend with the same issue, another friend found this KB

    Since this is a old thread I am not sure if this issue has been addressed in another way or if this KB would be a good fix for SQL 2005 / 2008 with some changes applied.

    Please take a look in the KB and let us know.


    Armando Lacerda
    Armando Lacerda
    Tuesday, April 07, 2009 3:34 PM
  • This thread was started in September 2006. So after three years is there a better way to achieve this with SQL Server 2008 or perhaps the soon to be released 2008 R2?
    Thursday, November 12, 2009 4:57 PM
  • Not possible
    Mike Hotek BlowFrog Software, Inc. Affordable database tools for SQL Server professionals
    Saturday, November 14, 2009 10:58 PM
  • This is possible for a single user login as you can only asign ownership of a database to a single user.
    Should be ok for most small hosting situations. This is how I achieved it under SQL server 2008...

    1. Right click the users logon under the users database if they exist and remove
    (Note: Do not remove the user from under the main security login)

    2. Run the following query

    USE <customersdatabase>

    ALTER AUTHORIZATION ON DATABASE::<customerdatabase> to <customerlogin>


    DENY VIEW ANY DATABASE TO <customerlogin>

    Sunday, November 15, 2009 10:54 AM
  • I have a similar situation.  Several customer databases on one server.  A couple of the customer users need SSMS access in order to create views and stored procedures for reporting.  I'd prefer that they only know about their own database and not see any of the other database names, even though they have no privilege to them.  I can't go the route of making them DBO of the database since in two cases we have multiple users from the same site/database using SSMS.  A way to address this in the future would be great.  Thanks.
    Tuesday, November 17, 2009 12:38 PM
  • Isn't the right way around this is to create instances for each separate group/application/user/whatever?

    That way each group can have anything up to sa rights on their own instance and not know about or affect any of the other groups databases?

    Obviously there will be some overheads but it seems to be the most secure and flexible option? You can have unlimited instances now can't you?

    Thursday, January 06, 2011 10:11 AM
  • This is possible for a single user login as you can only asign ownership of a database to a single user.
    Should be ok for most small hosting situations. This is how I achieved it under SQL server 2008...

    1. Right click the users logon under the users database if they exist and remove
    (Note: Do not remove the user from under the main security login)

    2. Run the following query

    USE <customersdatabase>

    ALTER AUTHORIZATION ON DATABASE::<customerdatabase> to <customerlogin>


    DENY VIEW ANY DATABASE TO <customerlogin>

    My friend, this works really good!

    thank you  a lot, you're a machine!


    tested on SQL Server 2008 R2.

    • Proposed as answer by Shivram S Thursday, February 12, 2015 5:06 PM
    Friday, April 08, 2011 3:51 PM
  • For me works (Sql2008R2)! Very simple and very useful.

    Thank you very much.

    Thursday, December 01, 2011 9:31 AM
  • Thank you very much. it is pretty good
    • Proposed as answer by faezeh.maadi Monday, January 02, 2012 8:44 AM
    Monday, January 02, 2012 8:44 AM
  • Yes. This works really well! I tested this on SQL Server 2012 R2.

    Thanks a ton!

    Thursday, February 12, 2015 5:07 PM
  • Firstly, I decided to respond to this particular entry in this forum mostly because of the answers supplied, this one seems to have satiated the larger selection of people who happened across it and decided to respond as such.  Secondly, my response below is in no means an antagonistic response to the solution provided.  Lastly before moving on, I hope somewhere out there a Moderator/Person is actually still being alerted to these posts (especially since at least someone other than myself has posted here in 2015).  If I am wrong, misunderstanding, or simply ignorant to some point made below I strongly encourage anyone to please set me straight and/or respond as such because I earnestly would like to understand any viable solution to this post's question, especially in regard to non-trivial (moderate to large Enterprise level) implementation.

    To The Point Of It All:

    Yeah, this "solution" still supposes that my only intention is to give <customerlogin> DBO access to <customerdatabase> ... when <customerlogin> is meant to have only, say role membership in db_datareader, this is the egregiously far away from my intended concern.

    I find that nearly a decade from when this thread starts (and 3+ versions of SQL Server as of this posting later) this simple question is still answered in complete oblivion of the point a bit unsettling.

    Case: I have a SQL <2005-2012,"2014"> Server (call it <server> here forward).  I have use for both Integrated Security *and* SQL User logins (call them <user> here forward) for multi-various reasons which should not matter to the point.  Explicitly to gain further insight only, the Integrated Security users can be either Local Users OR Domain Users OR Security Group(s), etc.  I have no reason for any specific <user> to have DBO rights on any Database as I am the DBA for <server>.  I inevitably encounter cases wherein I want/need to give a <user> direct access to <database> in some fashion.  However, I want the granular control to be able to give them something to actually use for that purpose.  Microsoft SQL Server Management Studio (SSMS) (or even nebulously <interface> here on) is, at times, the perfectly viable option depending on the <user>.

    Problem:  I can, generally within all <server> listed above, grant granular control to <user> for everything from CONNECT and LOGIN through explicit <database> and even explicit <action> (select, execute, etc.) on explicit <table,column,etc.>.  However, none of the proposed solutions address how to enforce the necessity to have <interface> only display to <user> what has been granted, despite all of <server> permissions available.  How can I ensure <user> not only DOESN'T have access to (which is sadly the harder problem which actually has solutions going all the way back through at least MSDE and SQL 2000) <server, database, action, table, column, etc.> which I haven't given access, and not only DOES have access to <server, database, action, table, column, etc.> which I have given access to, but ONLY display to the <user> the parts which are applicable to them.  Plenty of those pieces and parts have been implemented at least to the degree wherein GRANT, REVOKE, DENY, ALLOW, etc. semantics allow me some level of ability to the semantical intent I am ultimately trying to implement, but far less is *still* applying my intention to relate that access clearly to <user>.  

    In specifics to this forums question... Why is there a DENY VIEW ANY DATABASE syntax with no corollary GRANT VIEW DATABASE <user> and DENY VIEW DATABASE <user> to date to simply allow my intention to be met without having to make the <user> a DBO? I understand the cumulative access would immediately make the GRANT VIEW DATABASE <user> statement would likely yield a net gain of no change if the user were already prefaced with DENY VIEW ANY... but there still seems to be no earnest (nor straightforward) mechanism to accomplish (as a surface-level view) a simple task: Let the DBA be able to set any <user> access and usage to the degree to which their business policies are set, and in doing so, relate those settings to <user>.  The closest approximation to an answer I've seen thusfar without trying to convince a DBA to just make DBOs for all the above premises is something like

    Think in terms of how Microsoft handles System Folders and Files, Network Shares, Active Directory (GPOs, User/Computer ACLs, etc.) and/or more generally the philosophical difference in usage between a Windows 2000 desktop and a Windows 8.1 desktop (then apply domain semantics for an even clearer relation).  I realize the scopes are different and I realize there has to be a fine line between "too little", "too much", and "good enough"... but in this instance, I still cannot fathom the disconnect between what a Microsoft Domain Admin and a Microsoft SQL Admin fundamentally can and cannot do.

    Solution: ?

    Friday, March 27, 2015 3:09 AM
  • The solution? Not much, but you can add your vote to your Connect item:

    I will have to confess that I don't understand why Microsoft understands that this is important.

    But I believe that there is an improvement upcoming: the permission VIEW ANY DATABASE will actually starting working. See the bug

    Erland Sommarskog, SQL Server MVP,
    Friday, March 27, 2015 10:36 PM
  • Erland,

    Thank you for the response.  I actually happened to stumble upon the link you provided prior to my posting here, which is also why the votes are +180 instead of +179 now.  I actually intended to post some form of reference to this site and/or a sub-selection of my post here on that page, but I found myself unable to log-in to comment (oddly, considering it'd let me log-in to vote...).

    Of course, given the response, I wish there someone out there with enough clout to actually close out a 15 year-old "bug".  Provided an in-depth search along with the general usage, administration, and deployment of Microsoft SQL from MSDE 2000 through at least 2012 has taught me better than to be hopeful, by this point it becomes more of a defense mechanism to simply repress the thought by now.

    Sadly though, your second reference still only attempts to rectify the assumedly de facto answer provided in this forum and its own failure even to that end.  Nothing of that fix, to me, would still resolve anything other than still having to grant DBO to a user in order to accomplish what I was already responding to as being an egregiously incomplete (i.e. erroneous and negligent) answer here.  

    Any further responses, etc. are entirely welcome.  There are a few other rabbit holes to fall down across the Internet regarding these same premises, but I'm fairly sure you, particularly, don't really need more reference.

    Tuesday, March 31, 2015 10:02 AM
  • Old thread, but still an issue.

    Revoking VIEW ANY DATABASE to public at the server level is fine, but the continuing lack of a database-level GRANT VIEW DATABASE is really, really unfortunate.  It's a terrible assumption to make that we (admins) would not want this functionality, and an even greater assumption that the only ones that would need to view a given database is the dbo.  

    Microsoft really needs to add this functionality... it is sorely needed for those of us who have to securely manage shared instances.

    Tuesday, September 15, 2015 3:27 PM
  • However, this works perfectly if the user is the owner of the database. Look 
    that …

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see 
    master, tempdb, dbteste
    • From the “login1” session, you should see all the databases

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 16, 2015 8:15 AM
  • Following up on this thread - as of 3/3/16 there is still NO solution to the problem many of us our facing. 

    Problem Overview:

    On a SQL Server instance with multiple databases, we would like to allow a user to view only their database WITHOUT having to make that user DBO. 


    I currently have multiple users in single SQL Server environment. Each user has their own database and a login mapped accordingly. For security/privacy reasons, I would like those users to ONLY be able to view THEIR database. I have been able to accomplish this in a testing environment by Denying VIEW ANY DATABASE from the public role and then making the user that needs to view it the owner of the db. Problem is, I want to be able to accomplish this without making them the owner of their database.

    Is this possible? Any help/tips would be greatly appreciated.

    Specifications: Using Microsoft SQL Server 2012 (version 11.0.5343.0) on Windows Server 2012

    Thursday, March 03, 2016 9:28 PM
  • The development team is very aware of this situation and periodically reviews possible solutions. Unfortunately, solutions to this problem can cause some difficult new problems. Some of the issues include connections by people who are in Windows groups so SQL Server doesn't know if they can connect until they actually submit the credentials to the database. And the possibility of there being many, many databases on the server, so to list the databases, would mean submitting the credentials to all the databases before it populates the Object Explorer list. And there are problems with portability as databases are moved from one server to another, and contained database users that don't have logins in master. And I'm just scratching the surface here. So at the moment, Microsoft is aware of the issue and often thinks about possible solutions, but we aren't going to solve this soon. Sorry. The VIEW ANY DATABASE permission is the best we can offer right now, but we're not ignoring the issue.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, March 04, 2016 4:43 PM
  • Thanks Rick; looks like even SQL2016 CTP3 maintained the status-quo; even if you can’t do it for app databases, by chance would you be able to append “MSDB” along with master, tempdb and ownerof DBs, as a hotfix for SQL2012/14; at least users may hosts their own jobs with “VIEW ANY DATABASE” is been restricted by checking trust state of select has_dbaccess('msdb')? right now SSMS won't show-up SQL Server Agent tree view though MSDB is granted with reuqired role, but revoke/deny "view any database" from public or individual user.

    Monday, March 21, 2016 5:53 AM
  • Possible solutions:

    1) Allow users (who are denied VIEW ANY DATABASE) to explicitly object-explore a database name, if they know it.  Or to "add" explicit databases to the Databases tree.

    2) Allow creation of a DBA supplied view or stored procedure in the master database that can provide the result set used by SSMS.  Supply a default view or SP in master, that DBAs can modify or override.  If a dba messes it up, no big deal, it only affects SSMS.  Similar hooks could be provided to allow DBAs to define custom security.

    3) Allow users to specify a specific database or db,db,db list to view in the connection profiles and registered servers areas.

    4) Provide additional securables in [master] where logins can be assigned "view database" against a database name.  Whether the db exists or not can be dealt with in the SSMS view query by joining against sys.databases, which it probably has to do anyway.  (i.e. table with explicit loginname - databasename pairs, so DBAs can assign VIEW DATABASE permissions at the master db level, not the individual DB level.  Understanding this wouldn't persist across attach/detach, log shipping, and any other database specific constructs)

    5) Since it appears the default database of the login shows in the connection dropdown for queries, it should also show in object explorer.  An additional option in sys.logins to supply a comma separated list of additional databases could also populate this dropdown/Object Explorer's list.

    I host hundreds of databases per instances, but all my user accounts follow a naming convention.  I can tell from the username what DBs they should have access to.  There's no way you could build a built in solution to implement my solution (and everyone elses) in a generic way, but it would be trivial for me to do so.

    Once they try to access the db normal permissions would apply.

    These solutions should be relatively trivial for MS to implement.

    • Edited by Joe Gooch Friday, September 09, 2016 3:32 PM added options 4 and 5
    Friday, September 09, 2016 3:18 PM
  • 6)  Since db_owner is stored in master, and is also used as additional criteria to show databases - add an additional db_viewer column which maps back to a Server Role.  Users who should have explicit view of the database could then be added to the Server Role.

    That allows DBAs to optionally create server roles to create classes of access for sys.databases.  Anything beyond that is filtered by database permissions.

    Sunday, September 11, 2016 3:16 PM