question

victoradityan-3322 avatar image
0 Votes"
victoradityan-3322 asked Cathyji-msft commented

SSMS DENY ACCESS to EVERYTHING EXCEPT TABLES

**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**

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @victoradityan-3322,

Welcome to Q& A forum.

We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
victoradityan-3322 avatar image
0 Votes"
victoradityan-3322 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @victoradityan-3322,

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.

113291-screenshot-2021-07-09-145200.jpg

113189-screenshot-2021-07-09-145316.jpg

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

113292-screenshot-2021-07-09-145952.jpg

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.