0

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,

1
  • Just to be sure... what you are trying to fix here is the fact that, without permission, you are able to "expend" some databases (but cannot do anything else) when you are expecting a "DB is not accesible" error message. Is that correct ? – Dominique Boucher Aug 6 '20 at 13:50
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.