MS SQL Server 2016 - Database Security - Login Permissions

Question

I have an SQL Server 2016 database with Always On structure. I have a login that I called x. My x login's properties are:

• Default database: abc
• Server roles: public (ticked)
• User mapping: Map (ticked), abc database (ticked), default schema (dbo), permissions on abc database: db_owner and public
• Guest account enabled for: qaz
• Database role membership for qaz: public
• Securables: Permissions: CONNECT SQL, VIEW ANY DATABASE
• Status: Grant and enabled are ticked.

So my problem is:

• When I connect to this user and trying expand databases. I have no problem with my own database. But when I try to expand other databases, sometimes it gives "The database <db_name> is not accesible (true one) and sometimes I can expand a few databases but I can't see any table or objects and I can't operation on this databases (false one).

• Normally, it should be the same error ("The database <db_name> is not accesible) all of the other databases because I have no permission except my own database.

• How can I check the this case and how can I fix it?

Best regards,

Answer 1

There is a user "Guest" in each database. This user gives some permission for everyone.

My guess is that for the database where you can "Expend" the DB, your "guest" user in those database is probably enable and probably have the "Connect" to the database securable (and I hope not more then connect)

To disable that user, run this TSQL:

Use [yourDB]
go
REVOKE CONNECT FROM guest;
GO

Once this is done, your login x should receive the error you are expecting.