locked
How to restrict user from seeing available database on the SQL Server RRS feed

  • Question

  • Dear All,

    We can restrict users from accessing the databases from the security.

    But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible ?

    Thanks


    Anky

    Friday, May 22, 2015 8:09 AM

Answers

  •  -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
    USE master;
    GO
    ALTER AUTHORIZATION ON DATABASE::Dinesh TO hello;
    GO

    as per your requirement give more database.

    Yes, by making the user the owner of the databsae, he can see databases even without the VIEW ANY DATABASE permission. However, it may not be desirable to make the user owner of the database. Particularly, it will not work if there is more than one user that needs to see the databases.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 5:15 PM
  • we can restrict users from seeing the available databases

    Hello Anky,

    That's the VIEW ANY DATABASE Permission, by deault every user do have this permission, you could revoke (deny) it. But note, then the users "see" only the current database and where the user is the DBO, none else; but of course still can Access those wjere he has permissions for.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Friday, May 22, 2015 8:22 AM
  • Check it:-

    1)

    create LOGIN hello WITH PASSWORD='Dineshv@1234', CHECK_POLICY = OFF;


    -- Step 2:(deny view to any database)
    USE master;
    GO
    DENY VIEW ANY DATABASE TO hello; 


     -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
    USE master;
    GO
    ALTER AUTHORIZATION ON DATABASE::Dinesh TO hello;
    GO

    as per your requirement give more database.

    http://stackoverflow.com/questions/4085700/restrict-sql-server-login-access-to-only-one-database

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 1:48 PM
  • And of the option posted by Olaf isn't usable for you, consider using contained databases so you can login directly into the database.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Friday, May 22, 2015 6:10 PM
  • But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible ?

    Long-standing request that comes up every now and then. A very valid request, I think. Microsoft, on the other hand, seems to be a different opinion. Here are some Connect items you can vote on:

    https://connect.microsoft.com/SQLServer/feedback/details/682703/management-studio-should-only-show-the-databases-that-the-login-has-access-to

    https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database

    Today you are options are:

    1) Forget it. User can see all databases.
    2) REVOKE VIEW ALL DEFINITION - user can only see databases he owns, until he selects one he has permission to.
    3) Contained databases. Difficult to recommend, since it comes with restrictions, and it is a half-baked solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 22, 2015 9:16 PM
  • As other pointed  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

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 7:45 AM
  • "Yes, by making the user the owner of the databsae, he can see databases even without the VIEW ANY DATABASE permission. However, it may not be desirable to make the user owner of the database. Particularly, it will not work if there is more than one user that needs to see the databases."

    Indeed. Setting the user as the owner of the database is only doable/suitable for a very small number of cases where we see this issue.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 6:37 PM

All replies

  • we can restrict users from seeing the available databases

    Hello Anky,

    That's the VIEW ANY DATABASE Permission, by deault every user do have this permission, you could revoke (deny) it. But note, then the users "see" only the current database and where the user is the DBO, none else; but of course still can Access those wjere he has permissions for.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Friday, May 22, 2015 8:22 AM
  • And of the option posted by Olaf isn't usable for you, consider using contained databases so you can login directly into the database.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Friday, May 22, 2015 6:10 PM
  • But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible ?

    Long-standing request that comes up every now and then. A very valid request, I think. Microsoft, on the other hand, seems to be a different opinion. Here are some Connect items you can vote on:

    https://connect.microsoft.com/SQLServer/feedback/details/682703/management-studio-should-only-show-the-databases-that-the-login-has-access-to

    https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database

    Today you are options are:

    1) Forget it. User can see all databases.
    2) REVOKE VIEW ALL DEFINITION - user can only see databases he owns, until he selects one he has permission to.
    3) Contained databases. Difficult to recommend, since it comes with restrictions, and it is a half-baked solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, May 22, 2015 9:16 PM
  • As other pointed  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

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 7:45 AM
  • Check it:-

    1)

    create LOGIN hello WITH PASSWORD='Dineshv@1234', CHECK_POLICY = OFF;


    -- Step 2:(deny view to any database)
    USE master;
    GO
    DENY VIEW ANY DATABASE TO hello; 


     -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
    USE master;
    GO
    ALTER AUTHORIZATION ON DATABASE::Dinesh TO hello;
    GO

    as per your requirement give more database.

    http://stackoverflow.com/questions/4085700/restrict-sql-server-login-access-to-only-one-database

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 1:48 PM
  •  -- step 3 (then authorized the user for that specific database , you have to use the  master by doing use master as below)
    USE master;
    GO
    ALTER AUTHORIZATION ON DATABASE::Dinesh TO hello;
    GO

    as per your requirement give more database.

    Yes, by making the user the owner of the databsae, he can see databases even without the VIEW ANY DATABASE permission. However, it may not be desirable to make the user owner of the database. Particularly, it will not work if there is more than one user that needs to see the databases.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 5:15 PM
  • "Yes, by making the user the owner of the databsae, he can see databases even without the VIEW ANY DATABASE permission. However, it may not be desirable to make the user owner of the database. Particularly, it will not work if there is more than one user that needs to see the databases."

    Indeed. Setting the user as the owner of the database is only doable/suitable for a very small number of cases where we see this issue.


    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by Anky_007 Monday, June 1, 2015 9:02 AM
    Saturday, May 23, 2015 6:37 PM