SSMS DENY ACCESS to EVERYTHING EXCEPT TABLES

Question

**Hi
We would like to restrict the user access to only the TABLEs and not anything else not even Reports where they can run vulnerability assessment reports.

Currently the user can see the tables but can also access and see the Security with users, roles, schemas and create tasks for the database like export, import. when only CONNECT has been provided with select privilege to some tables.

Any information would be appreciated.
Thanks**

Answer 1

For more clarity in the OBJECT EXPLORER WINDOW, the user should only get to see DB tables but if they access any other objects like SECURITY it should NOT SHOW the USERS, ROLES.

And when right click on DB the user should not be able to perform TASKS like assessment, export, import etc.

Currently the user has only CONNECT permission then how come the user can see all objects apart from the tables like system views. In SECURITY all the users etc

Answer 2

SSMS is intended to be an administration tool, not something users use. There are many things SSMS will display which the logged in user may or may not have access.

The "tasks" you are trying to restrict are built-in functions of SSMS. The option is not something you can restrict from SQL Server, or SSMS. However, it may fail due to rights after selecting the option.

Answer 3

By default, if you say

CREATE USER MyUser

that user only has CONNECT permission, and can see nothing - not tables, not other users, cannot run vulnerability assessments etc.

So if you have user who can see more, this user has gotten these permissions from somewhere, for instance an AD group. Or, God forbid, someone has granted public all sorts of permissions.

A starting point, is to do this:

EXECUTE AS USER = 'UserWhoShouldOnlySeeTablesButSeesMore'
go
SELECT * FROM sys.user_token
go
REVERT

This query will list all security tokens associated with this user - directly and indirectly. That is, if this user is member of DOMAIN\SomeGrp, and this group has been granted db_owner, you will see db_owner for this user as well.

Answer 4

Hi @Vivek Singh ,

From your description, I have a test in your environment.

Create a SQL login Cathy, add the login in the public role, and did not map the login to any databases.

Using the below T-SQL to list effective permissions on the server for this login. The Cathy login could not access the user databases , but the login could access the system databases. For the list of logins in security, Cathy user can also only see sa and Cathy two logins. For server roles, this is system built in setting , the list also be shown. And tasks for the database like export, import are failed during the process, due to the login do not have the permission. Please below screenshots.

If you want to access the user database, please add the login in db_reader role as below screenshot.

Your requirement that only read the tables and can not access the Security with users, roles is impossible. I think you do not need to worry about that.

If i misunderstood your issue, please let me know.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.