locked
How to Create users in sql server for accessing only one database ( withour db_owner permission ) RRS feed

  • Question

  • Hello,

    We have several databases on SQL server 2012 . We would like to create 1 new user that can see database 'X' but can not see the rest of the databases. User should have only db_datareader permissions .

    Ps. We cannot change current owner of database X.

    Any idea ? Thanks.

    Kr,

    Marek

    Friday, April 3, 2015 1:22 PM

Answers

  • We have several databases on SQL server 2012 . We would like to create 1 new user that can see database 'X' but can not see the rest of the databases.

    Not possible. (Don't feel bad, this is a very valid request, and I don't understand why Microsoft don't act on it.) Here is a Connect item you can vote on:
    https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database

    You can do REVOKE VIEW ANY DATABASE for public or DENY this permission for this user, but the user will have to know the name, and type "USE mydb" in a query window.

    But even if you do DENY VIEW ANY DATABASE, the user can still find out the  name of all databases with help of the db_name() function. From what I gather, this will be addressed in the next version of SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 3, 2015 3:27 PM

All replies

  • you can give that user db_reader permissions on that database. if the user does not permission to access othe database, even though see them he cannot access.

    There is a way to not let the user see the database in ssms object explorer but it applies to all databases the user does not own. since the database is not owned by the user, even though the user has reader permisssions , he cannot see it in the object explorer..

     if you want that, try Deny view any database to <<user>>


    Hope it Helps!!

    Friday, April 3, 2015 1:30 PM
  • And this is exactly problem that I want to solve.

    When I have db reader access only to one db and apply following command : 

    Deny view any database to <<user>>

    I cannot see nothing in ssms object explorer.

    The question is how to change it to see only one db with db reader permission to this db in ssms ( without db owner ).  I'm sure that its possible.

    Kr,

    Marek

    Friday, April 3, 2015 1:57 PM
  • And this is exactly problem that I want to solve.

    When I have db reader access only to one db and apply following command : 

    Deny view any database to <<user>>

    I cannot see nothing in ssms object explorer.

    The question is how to change it to see only one db with db reader permission to this db in ssms ( without db owner ).  I'm sure that its possible.

    Kr,

    Marek

    i do not think it is possible, but wouold be curous to know if it  possible... when i looked for it, when i wanted, i did not find any way to accomplish..would be nice to know, if possible 

    Hope it Helps!!

    Friday, April 3, 2015 2:02 PM
  • USE [master]
    GO
    CREATE LOGIN [readonlyuser] WITH PASSWORD=N'test', DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [Test]
    GO
    CREATE USER [readonlyuser] FOR LOGIN [readonlyuser]
    GO
    USE [Test]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser]
    GO

    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

    Friday, April 3, 2015 3:02 PM
  • USE [master]
    GO
    CREATE LOGIN [readonlyuser] WITH PASSWORD=N'test', DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [Test]
    GO
    CREATE USER [readonlyuser] FOR LOGIN [readonlyuser]
    GO
    USE [Test]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser]
    GO

    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

    Helloo Uri - Yes, this script gives the user read access on the database - the question OP was asking and in my understanding is that - he does not want to see any other databases in the object explorer other than the one he has acces(read access) for the user. the user is not db_owner.

    Hope it Helps!!

    Friday, April 3, 2015 3:06 PM
  • We have several databases on SQL server 2012 . We would like to create 1 new user that can see database 'X' but can not see the rest of the databases.

    Not possible. (Don't feel bad, this is a very valid request, and I don't understand why Microsoft don't act on it.) Here is a Connect item you can vote on:
    https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database

    You can do REVOKE VIEW ANY DATABASE for public or DENY this permission for this user, but the user will have to know the name, and type "USE mydb" in a query window.

    But even if you do DENY VIEW ANY DATABASE, the user can still find out the  name of all databases with help of the db_name() function. From what I gather, this will be addressed in the next version of SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, April 3, 2015 3:27 PM
  • Ok, if so, see the below,however, this works perfectly if the user is the owner of the database.

    • 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

    Saturday, April 4, 2015 6:23 AM
  • Actually contained databases would behave that very way: contained users only see the database itself.

    But I would think twice or better three times if I really want to enable them. Make sure you know the implications for administrators.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Tuesday, April 7, 2015 8:55 PM