The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.
The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.
Hence by default the SSMS will show all the databases on SSMS.
If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,
DENY VIEW ANY DATABASE TO login_name
When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.
No comments:
Post a Comment