Need information user access level permission on sql server std 2017

Question

question

thrishank asked Feb 11, '20

Need information user access level permission on sql server std 2017

Hi,

I need to provide the access to a database to a user. This user need to see only assigned databases which is mapped to his account but not all the databases(It means I have multiple databases on same server which I cant explore to this user). I'm using SQL server 2017 and 2016 developer editions.

permissions sql-server-2017

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-02-11T12:13:32Z

anthony.green answered Feb 11, '20

You will need to play with the "View Any Database" permission, by default this is granted to public, so you will need to remove that. This then will revoke the right on all users on the server so you will then need to grant it back to the people who need it, leave it off from the people who don't need it.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-02-11T12:30:44Z

thrishank answered Feb 11, '20

Hi Anthony, Done the changes in securable tab but no luck the user is able to see all the database details

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green commented · Feb 11, 2020 at 12:36 PM

Did you revoke the permission from PUBLIC?

0 ·

anthony.green commented · Feb 11, 2020 at 01:04 PM

Tell a lie, the "VIEW DATABASE" option isn't part of it. You have to make the user the owner of the DB to view the database.

If you DENY VIEW ANY DATABASE TO <user> they will remove the ability to see any databases.

You cannot GRANT VIEW DATABASE TO <user> so that they see what you want to see.

The best bet would be to look at contained users instead.

0 ·

question