locked
Restrict other users from seeing different databases RRS feed

  • Question

  • Hello Experts,

    I have a requirement here and did some research but somehow not able to achieve the security fulfillment

    I have a Server where I have different client databases say client1, client2, client3.

    When a user from client1 logins in management studio, he should be able to see only client1 database and rest all databases should be hidden.

    I did tried below,

    USE master
    GO
    DENY VIEW ANY DATABASE TO  Client1

    go

    USE master
    GO
    ALTER AUTHORIZATION ON DATABASE::Client1 TO  [Client1]

    this works fine, but the user is made DB Owner by default, but I want to give just readonly access to user not a DB Owner.

    Any help truly appreciated. thank you.


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Tuesday, May 2, 2017 5:47 PM

Answers

  • No, I'm sorry but you are sandboxed in the contained database. So you need to be able to turn on partial containment for the "production" database and then create views (if needed) that uses local table, which you then give priviliges on for the users in question.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 2, 2017 11:18 PM

All replies

  • but the user is made DB Owner by default, but I want to give just readonly access to user not a DB Owner.

    Why do you add users by default to the db_owner role?

    Remove the database user from db_owner role and add it to db_datareader role instead.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, May 2, 2017 6:05 PM
  • Sorry, but this is just the way that SQL Server work...

    All logins can see all databases.

    Unless you deny view any databases, but now then can't see *ctany* databases (except master and tempdb, I believe).

    Except for the databases that the login owns, but this isn't practical for your purposes, according to your post.

    Perhaps you can use the "Contained Database" functionality? That allow somebody to "login dorectly to a user", without having a login. The session will be sandboxed to the databases specified in the connection string, and the session cannot see any other databases.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 2, 2017 7:33 PM
  • Hi Olaf, Tibork,

    I did below series of steps, however like you said it work.

    USE master
     GO
     DENY VIEW ANY DATABASE TO  test_security
    
    go
    
    USE master
     GO
     ALTER AUTHORIZATION ON DATABASE::client1 TO  [test_security]
     go
     ---now I am able to see just client1 database
     ---however i cannot see user under security
     ALTER AUTHORIZATION ON DATABASE::client1 TO sa;
     go
     USE [client1]
    GO
    CREATE USER [test_security] FOR LOGIN [test_security]
    GO
    
     USE [client1]
    GO
    EXEC sp_addrolemember N'db_datareader', N'test_security'
    GO
    USE [client1]
    GO
    EXEC sp_addrolemember N'db_denydatawriter', N'test_security'
    GO

    Hi Tibor,

    I liked the idea of contained databases and googled it and seems to be exactly what I want. But guess it has limitations.

    Can I do this way, create a contained database with just view which will point to my actual database?

    Like Client1 database where all my tables, procedures, all objects reside and I do all my dataload to this database so that I can back it up as well.

    then create a view database Client_view as contained database with just view which will point to tables in client1 database will this work?


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Tuesday, May 2, 2017 10:07 PM
  • No, I'm sorry but you are sandboxed in the contained database. So you need to be able to turn on partial containment for the "production" database and then create views (if needed) that uses local table, which you then give priviliges on for the users in question.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 2, 2017 11:18 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
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    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, May 3, 2017 7:06 AM
  • "However, this works perfectly if the user is the owner of the database"

    Yes, we have already established that, and Manjunath stated in the very first post that this doesn't meet the requirements.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 3, 2017 8:29 AM
  • thank you all, now I got the insight and Contained Database Option to research. thank you

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Wednesday, May 3, 2017 2:54 PM
  • Hi OLAF, this did not help :(

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Wednesday, May 3, 2017 2:55 PM
  • Hi TiborK,

    the contained database works perfectly fine for my scenario.

    My Only concern here now is will there be any performance impact using a Contained database, secondly since cross database connections are not possible how will the tempdb be used in this case? will the contain database be able to use the TempDB and any operations related to tempdb as any other database? many thanks in advance and for such a wonderful suggestion


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Monday, May 22, 2017 7:08 PM
  • I know of no performance issues, nor is there any issues with tempdb. There are however other restrictions which may bite you at some point. For instance, you cannot use Change Tracking in partially contained databases.

    Monday, May 22, 2017 9:48 PM
  • Hi Erland,

    thank you, very much. I was worried about performance and tempdb utilization. Since my end database is going to be OLAP, just one time daily load and I will never use features like Change Tracking, Repliation, Log shipping etc. So in that way I am good then?


    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

    Tuesday, May 23, 2017 6:13 AM
  • I suggest you read through the documentation topics before you implement. Among other things, restrictions are listed there: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 23, 2017 7:19 AM