question

AJ-AJ avatar image
0 Votes"
AJ-AJ asked SeeyaXi-msft commented

Restrict users to not have view or access to master database

Hi there,

I have 4000 users part of a AD group who have datareader access to ABC database. When im one of the AD group, and while i use SSMS to login, im able to see Master DB and select default objects under master. Master db contains only default SQL server items.

The objective is to secure master db and users should see only their assigned DBs (currently users can not access other db but can see other dbs using ssms).

I even created a dummylogin, default db - ABC, role as datareader and when i login as the dummyuser, i still see master db.

Any suggestions

How to keep master db visibility away from users?

How to give visibility only to the user DB(s) and do not show other DBs for which they dont have access.

Wondering if i need to turn off any public permissions?

Thanks

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

SeeyaXi-msft avatar image SeeyaXi-msft ·

Hi @AJ-AJ ,

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

Best regards,
Seeya

0 Votes 0 ·

4 Answers

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

See:
https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/

· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

AJ-AJ avatar image AJ-AJ ·

Thanks Tom. Based on the above link, the user still sees system db and master db. Is there a way that restrict users to not see any systemdbs? or am i missing something?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered AJ-AJ edited

i still see master db.

And what's the problem with it? Where do you see an issue?

By default any user an "see" (what every see could mean) any database and also common objects in master database, but always restricted to objects/metadata he has permissions for.

You can deny "View any Database" permissions, but that may cause other issues.
see
https://community.pyramidanalytics.com/t/63byya/how-to-hide-sql-databases-that-a-user-does-not-have-access-to



· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

AJ-AJ avatar image AJ-AJ ·

Thanks @OlafHelper-2800. As a part of security scan by the organization, they want to make sure data reader users do not have access to master db or select or view objects of master db.
There is nothing else in the master db other than the default however the thought is that unauthorized users (users who have data reader access only specific dbs assigned to them) dont wander around master db, select view objects.

Is that something which can be restricted?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

By default, the guest user is enabled in master, and I think that you can't disable it. Furthermore, guest is member of public which has permissions to a lot of stored procedures and system views. These procedures and views will perform permission checks, so that only users that are permitted to do actions to do see certain data are able to do that.

So, no, you should not do anything. That could stop things from working. Which system objects that are in master is not really a secret, since it is the same as any other SQL Server instance. (Unless you have put your objects in master.)

· 2
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

AJ-AJ avatar image AJ-AJ ·

Thanks @ErlandSommarskog . As a part of security scan by the organization, they want to make sure data reader users do not have access to master db or select or view objects of master db.
There is nothing else in the master db other than the default however the thought is that unauthorized users (users who have data reader access only specific dbs assigned to them) dont wander around master db, select view objects.

Is that something which can be restricted?

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog AJ-AJ ·

As a part of security scan by the organization, they want to make sure data reader users do not have access to master db or select or view objects of master db.

Can't be done. Well, you can revoke a lot of permissions to public in master. That may very well render the server unusable for your users.

Keep in mind what I said, in master, the permission check is inside the objects. And the objects themselves are public information. They are the same in all master databases in this world of this version.



1 Vote 1 ·
SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered SeeyaXi-msft commented

Hi @AJ-AJ,


Agree with Erland. > So, no, you should not do anything. That could stop things from working.

For Master database, please see: https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-ver15#restrictions
The following operations CANNOT be performed on the master database:

  • Adding files or filegroups.

  • Backups, only a full database backup can be performed on the master database.

  • Changing collation. The default collation is the server collation.

  • Changing the database owner. master is owned by sa.

  • Creating a full-text catalog or full-text index.

  • Creating triggers on system tables in the database.

  • Dropping the database.

  • Dropping the guest user from the database.

  • Enabling change data capture.

  • Participating in database mirroring.

  • Removing the primary filegroup, primary data file, or log file.

  • Renaming the database or primary filegroup.

  • Setting the database to OFFLINE.

  • Setting the database or primary filegroup to READ_ONLY.

And you can read the next section: Recommendations
The backup of master database is very important. I should point out that I am not saying here that backups of other databases are not important.

For other system databases, You can view them from the navigation bar on the left side of the previous link.
I mainly want you to know the Restrictions part of them.

Finally, please see:
Backup & restore: system databases (SQL Server)
Backup System Databases



Best regards,
Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

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

AJ-AJ avatar image AJ-AJ ·

Thanks @SeeyaXi-msft . As a part of security scan by the organization, they want to make sure users with data reader access should not access master db or select or view objects of master db.
There is nothing else in the master db other than the default however the thought is that unauthorized users (users who have data reader access, access only specific dbs assigned to them) dont wander around master db, select view objects.

Is that something which can be restricted?

0 Votes 0 ·
pituach avatar image pituach AJ-AJ ·

Good day RJ,

You have a good point to be warry about exposing system information. You can check for example in my post about cracking the Dynamic data masking, how I use the system tables in order to expose the data in the masked columns in the user table in a user database

https://ariely.info/Blog/tabid/83/EntryId/182/SQL-Server-2016-Crack-the-masking.aspx

Check the topic under the title: Advance Data Exposing

I also show how I use the tempdb in order to improve the cracking :-(

1 Vote 1 ·
SeeyaXi-msft avatar image SeeyaXi-msft AJ-AJ ·

Hi @AJ-AJ,

From my previous content, we can see that in fact, many of our operations on the system database are not allowed.
And be able to see the system database, which is the default.

Best regards,
Seeya

1 Vote 1 ·