Database user must display only one database and one schema

Question

I have a server on which there are 5 databases: A, B, C, D, E. I need to create a new schema on database A called ‘rpt’. Then I need to create a new user called ‘usr_rpt’. This user must have these characteristics: sql server login, password never expired, it must be able to connect to the server but by expanding the list of databases it must only be able to see database A. Of this database, it must only be able to see the rpt schema and only the views contained within it. It must not be able to see the other databases. It must not have access to see the tables in database A of other schemas. It must not have access to or see the views in other schemas. By see/access, I mean that when logging in with his credentials, on the left hand side by expanding ‘Databases’ he sees database A. Expand Tables and see nothing. Expand ‘Views’ to see only those in the rpt schema and can only do select. You must not be able to alter, drop, create, update, delete. How do I create this configuration?

• Dale K: What have you tried and where did you get stuck? Creating users and permissions is well documented.
• David Browne - Microsoft: Note, the database viewing bit isn't possible unless the user owns Database A, which is inconsistent with the other requirements. You can force the user to connect directly to Database A, but there's no grants that allow the user to connect to master and see only Database A listed. learn.microsoft.com/en-us/sql/relational-databases/…

Answer 1

You'll need to create your schema and user with admin rights high enough to perform the following actions...

deny the user access to master. this will also block visibility of master when the user logs into ssms.

USE [master];
GO
DENY CONNECT TO usr_rpt;
GO

deny the user access to databases that are not the one you desire

USE [master];
GO
DENY CONNECT TO usr_rpt ON DATABASE::DatabaseB;
GO

add as many of these as you need based on the number of other databases on the server

restrict access on Database A to your desired schema requires blacklisting the user on all other schemas:

USE [DatabaseA];
GO
DENY SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO usr_rpt;
DENY SELECT, INSERT, UPDATE, DELETE ON SCHEMA::sys TO usr_rpt;
GO

This will make the desired databases both inaccessible via queries, and not visible in the ssms ui

It will only make the schemas on the same database (Database A) inaccessible, through both queries and the ui. but they will be visible in the ui. any attempt to expand them will give an access denied error

Currently there is not a way to hide visibility of the schemas on the same database in which the user will have access to your new schema (Database A)

The only way to prevent visibility of everything in Database A also, is to deny the user connect to Database A. You would then need to create a new Database all together, set up your rpt_usr and rpt schema in it, and another user that already exists in Database A. That user will build the views that go into rpt. That user can reference the data in Database A

• J.D.: "This will make the desired databases ... not visible in the ssms ui - That's not true. "Currently there is not a way to hide visibility of the schemas on the same database in which the user will have access to your new schema" - That's also incorrect. Perhaps you inverted your thinking on those two things?
• user327706: I didn't say it makes the schemas desired not visible. it will make the other database objects not visible in the ui. You can completely hide database objects, you cannot hide schema while also giving access to Database A. those schema objects will be visible, but cannot be expanded. The "connect" permission gives access to the catalog of the database.
• J.D.: "I didn't say it makes the schemas desired not visible." - I didn't say you did say that. "it will make the other database objects not visible in the ui." - Nope. To do that you must REVOKE or DENY the VIEW ANY DATABASE permission appropriately. But this will hide all databases in the SSMS UI (it's all or nothing). "you cannot hide schema while also giving access to Database A" - Of course you can. By default you can only see the schemas you have access to within a database.
• J.D.: For further info: https://dba.stackexchange.com/a/345027/150011