SQL Server Management Studio (SSMS) is a software application developed by Microsoft that is used for configuring, managing, and administering Microsoft SQL Server. It provides a user interface for connecting to SQL Server, managing databases, writing and executing queries, and performing various administrative tasks.
SSMS is a powerful tool for database developers, administrators, and analysts who need to work with SQL Server on a regular basis. It is widely used in organizations of all sizes and industries that rely on SQL Server for their data management needs.
With its rich set of features and intuitive user interface, SSMS has become an essential tool for anyone working with SQL Servers. There are moments when it is necessary to hide some tables in Object Explorer. Assume we have the Person.Address table, which we are going to hide:
To do this, we need to add an extended property to this table:
EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value="Hide",
@level0type = N'Schema', @level0name="Person",
@level1type = N'Table', @level1name="Address";
GOOnce done, the result is as follows:
To change things back, just delete the property.
EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name="Person",
@level1type = N'Table', @level1name="Address";
GOI have tried to apply an extended property to columns and schemes. However, it did not work.
Advanced properties can hide tables from SSMS without access restriction.
DENY VIEW DEFINITION
VIEW DEFINITION is another way to hide tables in SSMS:
DENY VIEW DEFINITION ON Schema.Table TO UserName;Now, a user cannot see a table in SSMS. Actually, they will not be able to get a list of tables from sys.tables or INFORMATION_SCHEMA.TABLES
Filtering
You can filter tables in any database:
It is necessary to do the following to remove a filter:
Comparison of approaches
VIEW DEFINITION is more flexible. You can limit the visibility of the whole schema or a separate table, a view, one user or a role. It also affects the system view, which is very good.
On the other hand, VIEW DEFINITION requires an explicit access lock. In my practice, there have been cases when someone locked access for some users and forgot to ban others, which caused challenges.
Extended properties with more severe restrictions are applied to everyone and do not require an explicit ban for each user. In addition, they affect sysadmins.
dbForge Studio for SQL Server
In addition to the methods described in the article, specialized tools exist for working with SQL Server databases. dbForge Studio for SQL Server is one such solution, as it offers several advantages to both developers and database managers.
dbForge Studio for SQL Server is a versatile SQL Server GUI client available for MacOS and Linux. Its cross-platform compatibility enables users of these operating systems to construct and maintain SQL Server databases without virtual machines or compatibility layers.
- First, dbForge Studio for SQL Server delivers a straightforward and user-friendly interface that simplifies the process of designing, testing, and administering SQL Server databases. Its robust visual editors for database schema and object management facilitate the effective creation and maintenance of databases.
- In addition, dbForge Studio has extensive code completion and formatting capabilities, which dramatically boost productivity and reduce the likelihood of errors. Its SQL code refactoring capabilities facilitate the modification of database code without disrupting dependencies or generating downtime.
- It also has powerful data editing and management features that make data manipulation and analysis simple. Its data comparison and synchronization capabilities enable users to swiftly identify and resolve data issues.
- Notably, dbForge Studio for SQL Server is compatible with several operating systems, including MacOS and Linux, making it accessible to a broader audience of users. Its cross-platform interoperability enables users to access SQL Server databases regardless of the operating system they prefer.
In conclusion, dbForge Studio for SQL Server is a robust and adaptable application that provides several benefits to SQL Server database users. Its user-friendly interface, excellent code completion and formatting capabilities, robust data editing and management tools, and cross-platform compatibility make it an asset to any database development or administration team.
RELATED TOPICS
- Magento 1 vs Magento 2
- Product Review – Stellar Repair for MS SQL
- How to repair suspect database in SQL Server
- How to Optimize Your Database Storage in MySQL
- How to Remove Duplicate Lines in EmEditor (2023)
- MySQL Performance Tuning: Tips for Blazing Fast Queries