question

thrishank avatar image
thrishank asked

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.

permissionssql-server-2017
10 |1200 characters needed characters left characters exceeded

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

anthony.green avatar image
anthony.green answered

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 characters needed characters left characters exceeded

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

thrishank avatar image
thrishank answered

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

2 comments
10 |1200 characters needed characters left characters exceeded

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

Did you revoke the permission from PUBLIC?

0 Likes 0 ·

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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