none
Hide database names from unauthroized users in SSMS

    Question

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

     

    TIA,

     

    Barkingdog

     

     

    Tuesday, September 26, 2006 5:32 PM

Answers

All replies

  • Hi,

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

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Wednesday, September 27, 2006 6:37 AM
    Moderator
  • 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?

     

    TIA,

    Barkingdog

     

    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
    Moderator
  • 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.......

    0x010500000000000515000000AAB950D6A501....

    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?

    TIA,

    Barkingdog

     

     

     

     

    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.

    Thanks,
    Jack Richins
    SDE SQL Server

    Thursday, October 05, 2006 8:51 PM
    Moderator
  • 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.

    TIA,

     

    Barkingdog

    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.

    Thanks,
    Jack Richins
    SDE SQL Server

    Thursday, October 05, 2006 10:49 PM
    Moderator
  • 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.

    Thanks
    Laurentiu

    Thursday, November 30, 2006 9:50 PM
    Moderator
  • 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)

    Begin

    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 )

    End

    --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)

    End

     

    --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 http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a989ca87-660d-41c4-9dac-70b29a83ddfb/?prof=required#

    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.

    Thanks,

    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. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Saturday, November 14, 2009 10:58 PM
    Moderator
  • 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>

     
    USE MASTER

    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>

     
    USE MASTER

    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.

    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