Create SQL Login to View only one database on server

  • yogi123

    SSCrazy

    Points: 2765

    Hi All,

    I have one task to create a login, and user need to view only One Database, there are 20 other databases but requirement is for the user to view only one database and only 5 tables from 1000 tables.

    I tried to create a login and give Server Role Public and Database role db_datareader but user can see all databases, not accessible but still see the other database. and also see all of the tables from the database, which i gave him access.

    can anyone please guide me?

    Appreciate for any input.

    Thanks!

     

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    Offhand, I am not sure why they can see all of the databases if they only have permissions on the one.  A quick check online, it sounds like this isn't easily possible.

    https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/

    Which basically shows you how to hide all databases for a specific user, but the user needs to be the database owner in order to see it.  Same thing from this link:

    https://scatteredcode.net/sql-server-hide-database-list-from-a-particular-user/

    As for seeing all tables, you gave them db_datareader role.  that lets them read from all tables and views on the database.  You will need to either give them explicit permissions on the tables they need access to, or (the preferred way) create a new role and give that role permissions to those 5 tables and put the user in that role.

  • jcarranza 23978

    Ten Centuries

    Points: 1365

    Create the login.. make it public. Don´t give any access there.

    Go to the Database> Security ( add permisisons there under the account ) > select the account >

    add permissions under OWNED SCHEMAS and be more granular under SECURABLES.

    Test the account.

    Hope this helps.

    Attachments:
    You must be logged in to view attached files.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    jcarranza's solution will also work; my preference is still to create a role and use that role to assign permissions.  What happens to that user/login when they quit  and a replacement gets hired in?  Or a second person needs their permissions so they can be a backup or secondary person to do that job?

    Assigning roles is, in my opinion, a lot simpler.  Especially when you have multiple tables to work with for the permissions.

    For example, if your QA team needs access to tables A, B, C and D and your QA team is 1 person right now.  Giving that person access to tables A, B, C, and D is easy.  Then they get a second person on the team, not a big deal adding them again.  Now 10 years later, the team has 50 people in it and they now need access to table E, F and G.  That is now 50 users you need to add permissions for 3 tables.  That will be a huge pain in the GUI, and a lot of scripting to do in TSQL.

    Now, lets say you used a role called QA.  That role has access to tables A, B, C and D and initially has 1 user in it.  Jump forward 10 years and you have 50 people in that role and need to add tables E, F and G?  That's easy - add those 3 tables to the role and the users will have access.

    Now to make management of this even easier - Create a QA security group in AD and grant that access to the database as a login and user on the database and put it in the QA role.  AD group gets the new users and no changes need to be done on the SQL side!  Your source of truth for users is AD.  User changes departments from QA to IT, they lose access to those tables as soon as the AD admin makes the change.

    Also, future DBA's (and future you) will appreciate that you had the foresight to use roles rather than individual permissions.  Imagine someone asks you to "replicate" permissions from user A to user B.  That is trivial with role-based permissions, but a pain in the butt with object-based permissions.

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    One other point about AD groups...you can treat an AD security group as a 'role' in SQL Server instead of creating a role in SQL Server and adding the security group to that role.  This does not work well if you have separate roles in SQL Server and need to add those separate roles across users - but generally a security group should define the role a user has in the organization which will dictate what level of access and permissions they have in the database.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Sue_H

    SSC Guru

    Points: 90701

    yogi123 wrote:

    Hi All,

    I tried to create a login and give Server Role Public and Database role db_datareader but user can see all databases, not accessible but still see the other database.

    And the reason they can view any database is because by default, public has view any database permission. You'd have to remove that permission from public or deny that permission for that login.

    Sue

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply